Transform Multiple Columns at Once in Power Query | Advanced uses of Table.TransformColumns Function

Here is how you can transform multiple columns at once in Power Query. You'll learn some advanced application for using Table.TransformCoulumns() & List.Transform() functions in Power Query. Enjoy!

===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

===== LINKS 🔗 =====

===== CONTACT 🌐 =====

===== CHAPTERS =====
0:00 Intro
0:31 The Problem & the Data
1:47 Deep Dive into Table.TransformCoulumns
3:55 Deep Dive into List.Transform & Building Solution Structure
7:18 Solution Part 1 - Automating the Names of the Columns
9:43 Solution Part 2 - Transforming Multiple Columns
13:01 My Courses

===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
Official After The Fall YouTube Channel Below
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)

[RichardJones]

Flipping heck. A list within a list within a list sounds more like Inception! But yet you still explained it very well. Maybe you should try and explain the plot of Inception also!

[juancarlosleytonfernandez]

I am more and more amazed by your capabilities. It is impressive!

[shirleymoreman]

I really struggled with the underscore concept when I first started using Power Query but your explanations have been a massive help with this. It all sort of makes sense now. Thanks for another great video Chandeep

[skimpylemon]

You are an excellent teacher! Thanks again!

Could you do a non-edited video where you are just solving a problem on the spot so we can see your thought process and approach to solving a problem? I think it will your community see how you deal with something unexpected when building your queries.

[Robay]

Wow! This was very advanced and tricky! I can see you are well versed in M as well as Dax. Great solution. 👏🏾

[christopherhazel]

I think a key concept that makes this easier to understand is that the underscore is used twice but each instance is a different context resulting in it "pointing" to different items at different levels of the list within a list structure.

You just did a video on each and underscore. It might be a good follow-up video to explore this notion that each and underscore can be utilized more than once in the same expression but where they are seeing different contexts.

[abhijeet]

Thanks a lot for your detailed videos. You are a great teacher.
I have a very unique issue.
Through SAP I generate one report which has some redundant columns which needs to be added and transformed in a separate column. This needs to be done for some group of columns dynamically every month.

I have used List.SelectFields and List.FieldValues function. But the query is calculating each value in the columns instead of Column Operation.
Please suggest an alternative solution.

[billw]

Wow, thank you. I can't believe how much a time crunch can blind you to such a simple oversight.

Well I thought I understood, it all seems very straightforward. I created the ColList function for my source and have the right List of column names, I used it in the Column Reording function and it worked perfectly. But when I enter the exact command that you have at 12:37 which is:

= Table.TransformColumns(Source,
List.Transform(ColList,
each { _, each Text.Lower(_), type text} ) )

it displays the following Error:
Expression.Error: We cannot convert the value "column name" to type Function.
Details:
Value=column name
Type=[Type]

And it appears to have lower-cased the Column Name instead of the Column Values. What am I missing?

[JapjeetS]

Love your videos. Very informative, and your way of explaining works wonders for me.
Thank you again.

[paspuggie]

In my organisation we receive a dataset of over 70 columns and originally I would apply some trim & clean to those I knew that needed it. Over time that caused an issue because some other columns would require the same. I managed to use a similar technique to transform & clean every column before importing the data. It revolutionised the way I work now Goodly.

[oscarmendez-roca]

I think this method is such as better than any other, but what happen if the column names doesn't are equals?. Thanks Chandeep, you're the best!!

[carolshipley]

I am a beginner and I am 74years old. You have inspired me to continue learning.

[iancraigen]

This was very useful

I used

Table.ColumnsOfType(Source, {type nullable text}) to get the text columns in my source table, so I could then apply my text transform to all the text columns

My text transform was Text.Clean(_??"")

I needed to use the coalesce operator ?? because I sometimes had nulls in my text columns so I needed to feed the function a blank value instead.

[navisalomi]

You have succeeded in demystifying the each _ syntactic sugar. Excellent video.

[wayneedmondson]

Hi Chandeep. My first attemp was to use another technique previously learned from you to create the list of lists (below). I like your more advanced method using List.Transform. Always something new to learn. Thanks for the great examples! Thumbs up!!
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.ColumnNames(Source),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Column1], "Col")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Lower),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each type text),
#"Transposed Table" = Table.Transpose(#"Added Custom1"),
Custom2 = Table.ToColumns(#"Transposed Table")
in
Custom2

[DeronHuskey]

Great video! The breakdown of the process was easy to understand. Thanks for doing that.

[khurramkhan]

mind blowing and so well delivered ...thank you

[LuisCarlosChavez]

Awesome! I have a question; how can I apply a split column by delimiter in several columns at once?

[daxteoh]

Hey Goodly, love your video - really easy to understand.

[imtxnkg]

My dataset has many date and date/time columns. I want to use "change type with locale" en-us on all of them together instead of one by one. Also if a new columns with date or date/time is added, then automatically apply this transformation to it.