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 😉
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:
So how could I then multiply all my columns by 10 for example, as the symbol for multiplication is greyed out?
Simply check the columns to transform and select an accessible dummy-function. Ideally, it should contain as many arguments as the intended function, but that’s not mandatory. In our case I choose a function with 2 arguments (for the number and the multiplicator). Rounding -> Round… fits just nicely here:
I enter the number for the multiplicator (10) into the Decimal Places-field.
Now – are you asking yourself where to fill in the reference to the number itself? Then check out the M-code that has been generated automatically in the formula bar:
The query editor has set the reference to the number automatically and it is represented by the underscore (“_”). This represents the (only) function argument that is created automatically. Therefore, it used the syntax sugar “each”-keyword.
As you can see, all the code has been created for every field of the table. Therefore, the only thing we have to tweak now is the function itself. I change “each Number.Round(_, 10)” to “each _ * 10” by copy-pasting it into every column expression:
Using custom functions
A user in the Power BI forum lately asked me on how to apply my “RemoveHtmlTags”-function to his whole table. Therefore he would have to:
- Copy the function code from GitHub
- Create a blank query in the query editor
- Edit that query in the advanced editor and replace all existing code with the copied code
- Name that query “fnRemoveHtmlTags”
- Now you should check all columns and apply a dummy-transformation
- Lastly replace the function-part of the generated code with “fnRemoveHtmlTags” like so:
Are you wondering now where the “each” has gone? Actually, it is not necessary for functions with just one argument. Check this article for example for more details about it.
A function for more efficiency
If you want to apply the transformation to all of your table’s columns, the following function will come in handy. Just fill in 3 parameters (Table, Function and Type). Then at the end you can use the optional “ColumnNames”-parameter. In there you can provide a list of column names if you want to restrict the transformation to those columns only.
You should use the parameters as follows:
- Reference to the table itself
- Reference to the function
- Type of the columns to be transformed (attention: you have to use the proper type (without quotes) and not the textual representation)
- optional parameter: List of column names you want to limit the the transformation to certain columns
Enjoy and stay queryious 😉
Edit 20th December 2019: Please check out a much smoother version in Cameron Wallace’s comments down below!