Today I’ll present an adjustment to the Text.SplitAny – function in Power BI’s query editor or Power Query. The native function takes a string as an input and splits the text by every character that is contained in the string. This seems fairly unusual to me and I haven’t used that function very often.
But what I have come across fairly often is the requirement to split a string by a bunch of different (whole) strings (instead of single characters).
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”):
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: How Table.Alternate works Say I have the … Read more
When you merge tables with distinct keys in Power Query you will get the same result than the VLOOKUP-function in Excel returns (if this is new to you, check out this article for example: https://www.myonlinetraininghub.com/excel-power-query-vlookup) . But how to retrieve only the result of the first row, if the lookup-table has multiple rows with the … Read more
When you import Excel sheets who have empty leading or trailing columns and rows (showing null-values), you can substantially improve the complexity and speed of your import process with a simple trick: Remove the reasons for the empty trailing rows and columns 😉 Background Usually, when you import data from an Excel sheet, Power Query … Read more
Edit 2018-July-05: As it turns out I’ve missed the native function that exists with exact the same functionality: Table.TransformColumnNames Thanks to Tristan Malherbe for pointing this out 🙂 But anyway: If you want to understand what it does and how you could have written it by yourself – read along: The standard function to rename … Read more