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?
Solution
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.
Number 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:
Automatically generated formula
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:
Edited formula
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:
Remove Html Tags
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let func = | |
| (Table as table, Function, TypeForColumns as type, optional ColumnNames as list) => | |
| let | |
| columnNames = if ColumnNames = null then Table.ColumnNames(Table) else ColumnNames, | |
| Transformation = Table.TransformColumns( Table, List.Transform(columnNames, each {_, Function, TypeForColumns} ) ) | |
| in | |
| Transformation , | |
| documentation = [ | |
| Documentation.Name = " Table.TransformAllColumns.pq ", | |
| Documentation.Description = " Transforms all columns of a <code>table</code> with one <code>function</code> and one <code>type</code>. ", | |
| Documentation.LongDescription = " Transforms all columns of a <code>table</code> with one <code>function</code> and one <code>type</code>. Optionial <code>ColumnNames</code> to limit to a specific list. ", | |
| Documentation.Category = " Table ", | |
| Documentation.Source = " www.TheBIccountant.com https://wp.me/p6lgsG-2dQ . ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Imke Feldmann ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " TableTransformAllColumns( #table( {""TextColumn1"", ""TextColumn2""}, List.Zip( { {""123<code>456</code>"" ,""789<code>101</code>""}, {""ABC<code>DEF</code>"" ,""GHI<code>JKL</code>""} } ) ), fnRemoveHtmlTags, type text) ", | |
| Result = " #table( {""TextColumn1"", ""TextColumn2""}, List.Zip( { {""123456"" ,""789101""}, {""ABCDEF"" ,""GHIJKL""} } ) ) "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
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!
You could just write {} for the second argument of Table.TransformColumns and then use the 3rd argument (defaultTransformation) instead for your function. For the columns to get their types you would need to apply a function return type to your function being used in defaultTransformation. This could either be done via type checking or using Type.ForFunction. I think this would be advantageous because then the previous table is not referenced twice, (Table.ColumnNames and then Table.TransformColumns).
Hi Cameron,
Thanks, the trick with the default-argument is really cool!
How would one use the Type.ForFunction-function here?
Thanks and cheers, Imke
I made a function to explain. It transforms all columns except the one defined in the ColumnsToIgnore parameter
(Table as table
,ColumnsToIgnore as list
,Function as function
,TypeForColumns as type) =>
let
FunctionType = Value.Type(Function),
TransformOtherCols = Table.TransformColumns(
Table,
List.Transform(
ColumnsToIgnore,
(ColName)=> {ColName, each _}),
Value.ReplaceType(
Function,
Type.ForFunction(
[ReturnType = TypeForColumns
,Parameters = Type.FunctionParameters(FunctionType)],
Type.FunctionRequiredParameters(FunctionType))))
in
TransformOtherCols
And for further clarification, here is an excerpt from my pinned tweet on twitter:
”
We can also see function return types being used with Table.TransformColumns,
e.g. Table.TransformColumns(PreviousStep, {{“TextColumn”, Text.Trim},{“LeaveMeAloneColumn” , each _}}, Number.From) will:
1. Not only trim TextColumn but change the column type to type nullable text
2. LeaveMeAloneColumn is not altered (when you use each _ or (X)=>X etc… the column values are not transformed and column types are not ascribed either).
3. All the other columns are converted to numbers with the use of Number.From in the 3rd parameter, defaultTransformation. The columns types are changed to type nullable number, the return type of Number.From
“
Great! Thanks a lot Cameron!
Hi Imke,
Interesting post! But I’m new to functions in Power BI and I find what is being said above rather hard to grasp.
If I understand correctly, I can use your (or Cameron’s) function (function1) to call another function (function2) so that function2 gets applied to all the columns of the same data type in the table, which I all (so Table, Function, and TypeForColumns) specify in function1. Then, upon invoking function1 with the right parameters, it carries out the transformations of function2 on the specified table, right?
Now, in my case that means I have to write a function2 which replaces in each column of a table the values N/A, NVT, and Uknown with null. What would this function look like? Because I was able to implement this for two explicit columns in the steps of the table itself through the following piece of code (part of a larger M query for the entire table of course), but I don’t know how to convert it to a function so that I can apply it to all columns:
…
nonusableValues = {“N/A”, “NVT”, “Unknown”},
#”Replaced Value” = Table.TransformColumns(#”Filtered Rows”, {{“KVK_NR”, each if List.Contains(nonusableValues, _) then null else _}, {“POSTBUS”, each if List.Contains(nonusableValues, _) then null else _}}),
…
Can you help me out here? Thanks a lot in advance!
Kind regards,
Robin
Hi Robin,
I’ve written a blogpost about this here: https://www.thebiccountant.com/2020/07/15/transform-a-query-into-a-function-in-power-query-and-power-bi/
Hope this helps?
/Imke
hi, here a code fragment in M on how to transform multiple columns with few lines. I have spent lot of time trying to figure this out. This will divide multiple columns by the values stored in another column. You can change divide operation to any other 🙂
“Divide Columns” = Table.FromRecords(Table.TransformRows(+”Name of previous step”, (row) =>
lettargetColum = row[colum by which all the other will be divided],|
Transforms = List.Transform(
("column to be transformed 1", "column to be transformed 2", "colum to be transformed 3"),
(name) => [ name, (cell) => cell / targetColum))
in
Record. TransformFields(row, Transforms)
)
)
in
“Divide Columns”
In this case, Divide Columns is the last step.
Thanks for sharing 🙂
Hi,
This feature is very close to what I need, but my function has 2 parameters, not just the each _ and I can not find the right syntax to pass two parameters to the function
= Table.TransformColumns( #”Custom3″, {{“AP-GG”, fnTransformValue( each _, “AP-GG”), type text}})
returns a query.expression error – can not convert a value of type function to type number