Create list of accounts from NAV account schedules or COA totaling syntax

In this blogpost I’ll show you how to create a list of account numbers from the totalling syntax that you find in Dynamics NAV account schedules or chart of accounts for example:

1000..1090|1500|2000..2020

This string shall be transformed into a “real” list of account numbers in the query editor that can be used to select all accounts within those ranges.

  1. Split by pipe (“|”)

    • Text.Split(“1000..1090|1500|2000..2020”, “|”) splits the text into a list like so:

      NAV account schedules: 1) Split by pipe

  2. Split by dots (“..”)

    • In order to split each element of the list above by the 2 dots, I have to apply the split command to every item of the list. Therefore I use List.Transform like so:
      • List.Transform(SplitByPipe, each Text.Split(_, “..”)
    • This returns a nested list like below, with 2 list elements for those who contained a dotted range and just one element for the single ones like so:

      NAV account schedules: 2) Nested split by dots using List.Transform

  3. Create lists

    • In this step, the list of actual account number will be created. Again, I use List.Transform to transform the existing list by applying a function to each of its elements. The formulas required depend on the number of items in each list. If there is just one item in it (like the “1500” in the second row), nothing has to be done and the value can stay as it is (see yellow part in the pic below). But if the element contains 2 elements (like the “1000” and “1090” above), I create a list that starts from the first list element to the last. And as the elements in my list so far are in text-format, I have to convert them to number before when using them in the list creator ( {…} in green ):

      NAV account schedules: 3) Create list of accounts

  4. Flatten nested list of lists

  • Now that I have all numbers per list element, I just have to combine the 3 list into one. The function List.Combine does this like so:

    NAV account schedules: 4) Flatten nested list of list

Further steps

From here, you can convert that list into a table and merge it with your chart of accounts as an inner merge. That will eliminate all the numbers that have been created above who might actually not have a match in the accounts table.

Function

For convenience, I’ve converted these steps to a function that take the original string as the only mandatory parameter. There are 2 additional optional parameters that take different characters for the pipe and dots if you want to apply this logic to a syntax that used different signs:

Enjoy & stay queryious 😉

Return all occurrences of text between delimiters in Power BI and Power Query

The M-function Text.BetweenDelimiters does what it says and returns the text between 2 delimiters in the query editor of Power BI and Power Query. But irrespective of its optional arguments you can use to select specific occurrences, it will always only return one string found.

Problem

How to retrieve all occurrences of strings between in a text field like here for example: I’d like to get a list that contains “Python” and “R”:

“Meanwhile I prefer +Python!+ to +R!+”

Meaning all strings found between the delimiters “+” and “!+”.

Solution

This function will return the desired list:

How it works

In the first 3 mandatory parameters you fill the text to be analysed and the delimiters between the strings shall be extracted.

But there is also a 4th optional parameter that you will only have to use in the rare case that your 1st delimiter contains an “°”: My function works on the principle that a split by the 1st delimiter will actually just split the text on the 1st delimiters position. Now, when that string is also included in the 2nd delimiter, the text would also be split at the position of the 2nd delimiter. To prevent this, I introduced a Dummy that replaces the occurrence of the 1st delimiter-string within the 2nd delimiter. So in the case above, the 2nd delimiter “!+” will be replaced to “!°” before the splitting starts.

So far so good … BUT: If the 1st delimiter would actually be “°” instead of “+” here, my function would not return correct results. In that case, you’d have to fill in a value in the 4th optional parameter that is not contained in your current delimiters. So I tried to choose a Dummy-sign that will hopefully very seldom be used as the delimiter and you will never have to use this option 😉

The file to download contains the function and a query-version that allows you to follow each step if you want to see how the function is built.

Link to file:  TextBetweenDelimitersOccAll.zip

Enjoy & stay queryious 😉

Transform text with exceptions in Power BI and Power Query

Recently I picked up an interesting request to transform text with exceptions: Transform the words in a column to proper case, but keep certain keywords like in a defined list.

Problem: Transform text with exeptions

Say you have a list with specific terms that shouldn’t be proper cased like so:

And you want to proper case the following column:

Convert to proper case with exceptions

So I proper case each word that is not contained in the “KeepTable”, identify the elements in the “KeepTable” in a case insensitive way and transform them into the syntax that’s specified in the “KeepTable”.

Solution

The overall strategy is to convert everything to proper case first and then use a translation table to convert the keywords from the table back to their desired values. The following steps show how to do it:

First I split the column with the values to be proper cased into nested lists:

Split text of each row into a list

Then I proper case each element in the list (including the ones that should actually be excluded!):

Proper case every element within the list

Now I just have to translate the proper cased keywords from my “KeepTable” to their original values. Therefore I need a translation table like from my multiple replacements solution and use the technique from this blogpost to achieve the desired result:

Starting from the “KeepTable” I add the proper cased “From”-column like so:

Then reorder the columns, so that the “From”-column comes first:

Reorder columns, so that “From” comes first

Then I transform this to a list of list so that it can be used by the replacements function:

Transform to list of lists

This list of lists can now be used in the translation operation. Therefore I reference the step where I have proper cased the original column (green) and perform the translation (yellow):

Replacement function translates proper cased word back to the original values

The last step is to stitch back the list into a text-string:

Stitch the list back together into a text-string

 

Please check out this file to follow the solution along:  CapitalizeWithExeptionsUpload.zip

You will see that this a solution that is mostly achieved by using the UI and adding columns to the table.

Enjoy and stay queryious 😉

Memory efficient clustered running total in Power BI

Today I want to share a scenario where a running total calculation in the query editor saved a model that run out of memory when done with DAX:

Problem

The model couldn’t be refreshed and returned out of memory error with a calculated column in the fact table of over 20 Mio rows (from a csv-file). A running total should be calculated for each “JourneyID”, of which there were over 1 Mio in the table itself. This rose memory consumption during refresh by over 300 % – until it finally errored out:

Besetzung =
CALCULATE (
    SUM ( Fact[Entries] )
– SUM ( Fact[Exits] );
    FILTER (
        ALLEXCEPT ( Fact; Fact[JourneyID] );
Fact[StopId]
<= EARLIER ( Fact[StopId] )
    )
)

Solution

In the query editor, I grouped the fact-table by “JourneyID” and choose “All Rows”:

Group in the query editor to effectively partition your Fact-Table

Due to the fact that the fact table was sorted by the “JourneyID”, I could use GroupKind.Local and manually tweaked the resulting code like so :

Table.Group(#"Changed Type2", {"JourneyID"}, {{"All", each _, type table}}, GroupKind.Local)

Without this option, the calculation would have also errored out.

This effectively created table partitions, with one row for each “JourneyID”:

One table per JourneyID

I then added a custom column where I used my superfast M-code for running totals, referencing the column “All” with the partition in it:

Table.AddColumn(#"Grouped Rows", "Custom", each fnRT([All], "JourneyID", “Movements”))

Function Code

This M-code for running totals is amazingly fast (within M-dimensions 😉 ) and particularly plays its strengths on large sections for aggregation.

Performance

Memory consumption rose from around 1,700 MB to 2,000 MB, all very stable. Of course, refresh duration rose (by 150 % for that table). But for that use case, it was an acceptable price to pay.

Enjoy & stay queryious 😉

Unravel cumulative totals to their initial elements in Power BI and Power Query

Recently I came across an interesting request where someone wanted to un-cumulate their quarterly YTD-figures (green) into their single quarters values (red) like so (“Unravel cumulative totals”):

Task

Retrieve every Quarters Amount from the Quarter To Date values (“YAmount”)

Method

To retrieve this value, one would have to start with the first value in the year. This is also the value of the first quarter, but for the 2nd quarter, one would have to deduct the value of the first quarter from the cumulative value of the 2nd quarter. So basically retrieving the previous cumulative row and deduct it from the current cumulative row. Do this for every row, unless it’s the start of the year or belongs to a different account code in this example:

Grab previous cumulative values, but only within the valid ranges

(Although for the data given in the sample, it would be sufficient to just take the year as a discriminator, but to be on the save side, I would suggest to include the different accounts as well)

Solution

Fortunately I’ve already written a function to grab the previous rows with lots of bells and whistles, that also includes the option to include grouping parameters. So if you copy the function code to the advanced editor of an empty query and name this “fnGetPreviousRow”, you just have to add a new step with the following code:

fnGetPreviousRow(#"Changed Type", null, {"YAmount"}, {"Account code", "Year"}, null, null)

Add a step to call this function (don’t go via “Add a column” here !!)

Call function (Previous stepname: red, Amount column: yellow, Grouping columns: green)

 

This will retrieve the previous row from the cumulative “YAmount” within every combination of “Account code” & “Year” and fill in nulls in the respective first rows. So when you then add another column that subtracts the new Value from the CumTotal, you will retrieve nulls for the first rows. This is not the desired outcome and I suggest to go back to the previous step -> check the “YAmount.Prev”-column and replace “null” by “0”. After that the calculation returns the correct result:

Result with single quarterly values (“Unravel cumulative totals”)

File to download

You can download the file to follow the steps:  Unravel cumulative or running totals

Enjoy & stay qeryious 😉

Comparing Table.AlternateRows with List.Alternate in Power BI and Power Query

I must admit that I had more than one unsuccessful attempt to try to fully understand how the List.Alternate-function works. What helped me at the end, was the function Table.AlternateRows. It pretends to be similar to List.Alternate, but holds some surprises that I will uncover in this blogpost:

How Table.Alternate works

Say I have the table below and want to retrieve just the letters that appear in every 2nd row:

Table.Alternate – Remove every other row

I find the dialogue that appears very helpful and intuitive:

It clearly is a removal operation and here I want to remove the 1st row from my table (“1”), and just one at a time. Also want to keep just one row (“A”) before the next one is removed (“2”)  and so on.

In the formula bar, this step will be translated into this M-code:

Table.AlternateRows(Source,0,1,1)

If you would have expected it to be translated to: Table.AlternateRows(Source,1,1,1) instead, you might have forgotten that the M-language in Power Query starts to count at 0, so the first row to remove is expressed by the 0 here.

List.Alternate should work similar

So if my input is a list instead of a table like below, I should expect a similar result than the sample above if I tweak the code a bit, shouldn’t I?

List.Alternate – produces a different result

But hey: What’s wrong here? Not a single element has been removed from the list !!

So let’s have a look into the documentation:

List.Alternate – Function Documentation

and compare it with the Table.AlternateRows documentation:

Table.AlternateRows – Function Documentation

Hm – at least we have one match here: The “offset” parameter is included in both functions. But it is the first (number) parameter in the Table-function and is at the last position in the List-function. So let’s move it around then like this:

List.Alternate – same result with different parameter order

There we are 🙂

So the order of the function parameters is different here. Also the other parameter names are different and their description. I find them much easier to understand in the Table function and of course, the function dialogue there helps to understand what shall happen as well.

Enjoy & stay queryious 😉