This is not the first time I have shared this concept. In my previous article I showed how it is possible to add a prefix to every column in a table. This article today is slightly different. Today I am removing text from multiple columns all at once using some M code. The trick you need to learn to solve this problem is “how to create a list of lists”.
Watch the video to see how it works in detail.
Hard Coded List of Lists
In Power Query, you can use the M language curly braces to create a list as shown below.
= {“A”, “B”}
And you can embed many lists inside other lists, like below, to create a list of lists.
= {{“A”, “B”} , {“C”, “D”}}
Creating a List of Lists from a Table
You can also create a list of lists from a table in Power Query.
- Load (or build) a table with 1 or more columns (2 columns in this case)
-
Turn the table into a list of lists by wrapping the code inside the M function
= Table.ToRows()
Once you create your own list of lists, you can use the one you created to replace any list of lists generated by the UI.
Learn Better, Faster at Skillwave.Training
If you would like to learn more about Power Query, Power BI, Visualisations, DAX and Excel. Look no further than as one of the best learning resources available online.
Here is a link to the previous blog on this topic.
Awesome as always.
This is where I like reaching into the M-Code and doing this in one line:
= Table.TransformColumnNames(AllData, each Text.BeforeDelimiter(_, ” (“))
The Text.BeforeDelimiter() function will ignore text without the pattern.
In truth, my first attempt was using List.Zip() (before I remembered the Table.TransformColumnNames() function:
= Table.RenameColumns(AllData,List.Zip({Table.ColumnNames(AllData), List.Transform(Table.ColumnNames(AllData), each Text.BeforeDelimiter(_, ” (“))}))
Here you get the list of the original column names, Table.ColumnNames(AllData)
Then using the same list, you use that in a List.Transform function looking for text ” (“.
You enclose both those lists into {} for List.Zip.
List.Zip then takes both those lists creating your “list of lists”.
very nice code. Thanks for sharing.
Very good trick, thanks Matt for sharing.
Mauricio in Florida.
Matt,
I’ve found this useful for appending tables that may have slightly varying names (for whatever reason) such as “Countries” v “Country”, or “Surname” v “Last Name”.
The append is much neater if the columns align.
Great tip Matt! Thanks.
Great Tip Matt
cheers from Brazil.