You can apply simple transformations to multiple columns at once in Power Query using the UI only. In this article I show how you can apply advanced transformations on multiple columns at once instead. You can also use this to use custom functions instead. And lastly for the lazyefficient fans of custom M-functions: You will get a new “TranformAllMyColumnsAtOnceHowILikeIt”-function as well 😉
Background
The Transform-tab in the query editor is sensitive to the columns you select. So if you select multiple number columns for example, some number transformations will be greyed out and are therefore not accessible:
Some symbols are greyed out
So how could I then multiply all my columns by 10 for example, as the symbol for multiplication is greyed out? Read more
In a previous post I introduced the concept of a function that searches for an occurrence of a character or string within all columns of a table. Here I share the full “Table.ContainsAnywhere” – function with parameters for many useful options.
Function parameters and options
The first parameter “MyTable” refers to the table to search through
The 2nd parameter “MySearchStrings” can be either a text field or a list of strings to be searched for. The function will take care of any of these cases automatically.
If the 2nd parameter is a list and this 3rd parameter is null or not speified, the function will return true if any of the list items is found within the table. But if set to “All”, all list items have to be found somewhere in the table for the function to return true.
By default, the search will be made in a case sensitive mode (as this is the default-mode in Power Query). But any entry into the 4th function parameter will turn this to a case insensitive mode instead.
By default, the string or list entry has to match fully with any entry in the table. Again, any entry in the 5th parameter swaps that to a partial match.
The native Table.Contains-function in Power Query tells you if one or more strings are included in one or more of its columns. But you have to be specific about which strings you search in which column. But what to do if you want to search a string in all of its columns instead? Use my new Table.ContainsAnywhere function.
Problem
In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:
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.
Problem
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).
When you search the web for solutions to reference previous or next rows in the query editor of Power BI or Power Query, you will find many solutions that base on an added index-column. But the problem with these solutions on large tables is that performance will range between slow and extremely slow. In this post I’ll show a faster method with function Table.ReferenceDifferentRow .
Basic mechanism
This new mechanism doesn’t use an index that is either used to merge the table with itself or to be referenced as a new row index. Instead, I “simply” add one table next to the other. To retrieve the previous row from a table, I reference the original table, delete its last row and add a blank row on top. That will “shift” the first row to the second row. Then I “put” this table just right to the original table, without referencing a key or applying any sort of logic. This will speed up the process considerably:
Shift table to reference different rows
The key of this method is the Table.FromColumns-function: It creates a table from a list of columns in the order of the columns in the list. So I just have to find a way to turn 2 tables into 1 list of columns:
will do this job. Table.ToColumns will turn a table into a list of columns and the ampersand (“&”) will concatenate the lists from both tables.
The functions
The basic function that references the previous row of a column looks like so:
Reference your table in the 1st parameter and the give the column name whose previous row you want to see as the 2nd parameter.
But I’ve also included this basic mechanism into a handy function with some bells and whistles: “Table.ReferenceDifferentRow” Please be aware, that this function is not as fast as the main simple function!
How the Table.DifferentRow-function works
The only mandatory parameter is your table and then it will return a table with the previous rows values of all columns. So fnTable_ReferenceDifferentRow(MyTable) will return the result from above.
The default-value for this parameter is set to -1 to return the previous row if you omit it. If you want the values from the next row instead, fill in 1. 2 will return the overnext and -2 the pre-previous row. This is what fnTable_ReferenceDifferentRow(MyTable, -2) returns:
-2 will return the pre-previous row
You probably just need one or a few columns/fields from the previous row: In the 3rd parameter you can enter a list of column names to be returned: fnTable_ReferenceDifferentRow(MyTable, null, {"Value"}):
Select specific columns
Quite often the previous values shall only be returned within a group of rows. (That’s when you use [MyColumn] = EARLIER([MyColumn]) in DAX). You can enter a list of group-columns in the 4th parameter of this function: fnTable_ReferenceDifferentRow(MyTable, null, null, {"Product"})
Group by columns
By default, the suffix “.Prev” will be added to the new column names. Use the 5th parameter to change if needed. In this example, I reference the row below using “1” for the 2nd parameter: fnTable_ReferenceDifferentRow(MyTable, 1, null, null, "Next")
Changing suffix and referencing next row
If performance is still too bad, you can try to buffer the input table. Any value in the 6th parameter will do that for you (although I haven’t seen a performance improvement for my test cases).
How to apply the function
Please check out this short video on how to apply the function
