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. Read more
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.
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 “!+”.
This function will return the desired list:
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.
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”.
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:
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] )
)
)
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”):
Retrieve every Quarters Amount from the Quarter To Date values (“YAmount”)
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:
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:
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.
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 😉