Transform Column Names in Bulk in Power Query

This video shows you how to transform column names in bulk. Whether you want to add a prefix, capitalise each word or conditionally replace column values. This video has got you covered. You will learn several different ways on how to achieve this.

WRITTEN BLOGPOST:

Master Functions and Syntax in M

ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.

SUPPORT MY CHANNEL

SUBSCRIBE TO MY CHANNEL

LET'S CONNECT:

Thank you for your support!

00:00 Introduction
01:02 Understanding Table.RenameColumns
06:19 Using List.Zip
07:06 Method 1: Rename Camelcase
09:03 Method 1: Replace underscores
11:05 Method 1: Add Prefix
13:31 Method 2: Rename Camelcase
15:47 Method 2: Replace Underscores
16:22 Method 2: Add Prefix
17:16 Method 2: Conditionally Rename Columns

#rename #powerquery #bigorilla

[pravinshingadia]

One of the best channels on power query around - well done

[lpenatti]

This is the kind of video I love to watch, well explained, useful and powerful! Thanks for sharing it

[paulgallagher]

This is brilliant and will save me so much time. Thank you. :)
And as an added bonus it all folds.

[andrewwood]

THANK YOU! Used this to help rename a column to a new name if it contained a word in the original name.

[VipinThakur]

Amazing video. Little complex but you explained it very well. Thanks for sharing. You got a subscriber 😊

[KhajonsakLun]

Thank you very much from Thailand. I really like these technics.

[navisalomi]

Amazing vid. The Table.TransformColumnNames fx was a bomb.

[kristianbras]

Bedankt Rick! Echt top dat je deze video hebt gemaakt. Op dik 60 kolommen scheelt me dit een hoop tijd ;)

[marcinm.]

Thank you. This is very useful and pretty cool solutions👍

[wimmosterdijk]

awesome. definatly need to invest more time in Power Query as there is so much to gain

[ExcelInstructor]

and here is my method to Transform column types dynamicly :)
Table.TransformColumnTypes(Source,
Table.ToColumns(
Table.Transpose(
Table.AddColumn(
Table.FromList(
Table.ColumnNames( Source )), "Type",
each
if
Text.Contains(Text.Lower([Column1]), "date")
then
type date
else
type text))))

[yeknafaskesh]

Excellent video and explanation. Thank you.

[karimallahwala]

Thank you very much.These Tipp are very helpful

[gospelmoto]

excellent indeed! but a little complex.

[funriderhoermi]

Amazing how easy life can be when you know where to look. Referring to the last part of your video on the subject of dates. How can I replace header with correct floating date. Belowan example:

Is there a way to use a wildcard in functions. Example: "Thur 20-Apr €" is the text. I would like to search as follows: find "Thur wildcard €" and then I want to replace it with "Thur €". In addition, this should be possible for multiple promoted headers.
Best Hans

[txreal]

Thanks 🙏Rick!
Would you have time to do a video on bulk replace in the data rows (instead of manual Conditional Column with multiple if...then...else if..)?
I often copy & paste multiple if..then.. else if in PQ Editor, and just change the text for each, like below
each if Text.Contains([Column1], "Old Text ") then "New Text"
else if Text.Contains([Column1], "Old Text2") then "New Text2"
else if ...
else null)

[raeesg]

Thank you for this! - which is faster: replacing underscores, or adding spaces inbetween capital and non-capital letters?

[charliemcpherson]

Can this be done while maintaining a DirectQuery connection? Adding the steps to my query prompts a message "This step results in a query that is not supported in DirectQuery mode". Any alternative to maintain DirectQuery capabilities?

[dirkstaszak]

Interesting, I use a different way in order to be even more flexible, I have set up a column table, where I can steer if all columns or only a few are used and of course the name of the column can be set too. That gives the opportunity to use one data extract for different purposes

[williamrhopkins]

Excellent -- I learnt about List.Zip. How about a function that takes a table, takes any of the column formats (CamelCase, Underscores, Spaces) and gives a table with the column names in a canonical format say all words capitalized separated by spaces. I think that is doable.