Transform Column Names in Bulk in Power Query
HTML-код
- Опубликовано: 2 авг 2024
- 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:
gorilla.bi/power-query/transf...
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
SUPPORT MY CHANNEL
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ruclips.net/user/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
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
To learn more about the syntax of Table.RenameColumns, make sure to check out: powerquery.how/table-renamecolumns/
One of the best channels on power query around - well done
This is the kind of video I love to watch, well explained, useful and powerful! Thanks for sharing it
Thank you very much from Thailand. I really like these technics.
This is brilliant and will save me so much time. Thank you. :)
And as an added bonus it all folds.
Amazing vid. The Table.TransformColumnNames fx was a bomb.
THANK YOU! Used this to help rename a column to a new name if it contained a word in the original name.
Amazing video. Little complex but you explained it very well. Thanks for sharing. You got a subscriber 😊
Bedankt Rick! Echt top dat je deze video hebt gemaakt. Op dik 60 kolommen scheelt me dit een hoop tijd ;)
Great video. Thanks for that.
Well explained. Thanks for sharing.
This is next level. Awesome thanks
Excellent video and explanation. Thank you.
You are welcome!
Clever solution! Thx
Glad you like it 👏
Great information! Thank you
Glad it was helpful!
Thank you. This is very useful and pretty cool solutions👍
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))))
excellent indeed! but a little complex.
Great Video Rick
Thanks Johan 🙏
Great trick!
Very nice tutorial 👍
Brilliant 👍
Fantastic Rick
Superb!
Amazing
Thank you very much.These Tipp are very helpful
Welcome 😊
Just amazing!!!
Wow wow, thanks for the kind words !! 🚀😁
awesome. definatly need to invest more time in Power Query as there is so much to gain
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
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)
Thank you for this! - which is faster: replacing underscores, or adding spaces inbetween capital and non-capital letters?
My guess would be replacing underscores. It's a bit more complex to check for capital/non-capital!
@@BIGorilla thank you!
14.45
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
Hi Dirk! I’m interested in learning more. Do you have a separate table with column names so you can merge them and do a lookup what renaming should happen?
How do you configure this in practice?
@@BIGorilla sure how we gonna do this?
Can you describe it in a comment@@dirkstaszak4838 😁
I was asked to explain a litte more. I try:
The data source is a csv, txt or even Excel file. In Step one I do get rid of the headers if there are any.
Step two is then to determine what columns I want to keep (see below). I generate a list with all columns that do not contain the key word skip. This list is then used.
Step three is then to rename the columns from that same list.
I need to import the below table as basis for the above steps
SpaltenNr NewName Content
Column1 BuKr Company Code
Column2 SK Account
Column3 SKIP Trading Partner
Column4 PSP PSP-Element
Code for Step two
dColPLKeep
let
Source = Excel.CurrentWorkbook(){[Name="dColPL_Details"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SpaltenNr", type text}, {"NewName", type text}, {"Content", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([NewName] "SKIP")),
SpaltenNr = #"Filtered Rows"[SpaltenNr]
in
SpaltenNr
Code for step three
dColPL
let
Source = Excel.CurrentWorkbook(){[Name="dColPL_Details"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SpaltenNr", type text}, {"NewName", type text}, {"Content", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([NewName] "SKIP")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"SpaltenNr", "NewName"}),
TransposeTableforList = Table.Transpose(#"Removed Other Columns"),
Custom1 = Table.ToColumns(TransposeTableforList)
in
Custom1
use of code in main query:
KeepCol = Table.SelectColumns(RemOldHeading,dColPLKeep,MissingField.Ignore),
AutoColName = Table.RenameColumns(FltAcc,dColPL,MissingField.Ignore),
Have fun and of course comments are more than welcome
@@dirkstaszak4838 Wowa, thanks for sharing that. It really shows how you’ve managed to use different Power Query concepts to work for you.
It’s a bit of code to put in, but if flexibility is what you need, it’s probably worth the effort.
I appreciate you dropping the example under the video, I’m sure other will appreciate learning about it as much as I do.
Thanks!🙏
0:10 lets see if thsi is the method Im using :)
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.
You should be able to create a function for that. It can apply three different transformations in a particular order and apply it to the column names. Will you give it a shot Will?
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?
how can I rename table column names with their position in the table rather than the actual column names' list?
for example:
col1 | col2 | col3 | col4 ....
x | y | z | w .....
so, rename columns, col1, col2, etc. based on their position {0, 1 , 2, 3} to {target1, target2, target3, target4, ....}
Issue: What would be the solution if Column name and posting of the column keep changing.
Ex:
Column A: Name, Column B: E-mail.
Next time I get the data in this format
Column A: E-mail, Column B: Full Name
This is the big issue I experienced. Do you have any solution for this??
May I ask how come you always use PascalCase in renaming your query steps?
Just curious 🤔
When you reference a step without spaces you can simply write its name.
So referencing TableName can be done with TableName.
Yet when there’s a space or special character you need extra formatting
Referencing Table Name requires you to write #”Table Name”.
I feel it’s easier to use PascalCase for the clarity of the code 🙏
@@BIGorilla Thanks for your prompt & helpful answer.
Suggestions for the scenario where I start with snake_case and then replace the "_" with " " (this I know) and then want to capitalize only the very first word. ie, "sales_item" transformed to "Sales item".
Hi JvdWaa - I would change the code to:
= Table.TransformColumnNames(
Source, each
Text.Upper( Text.Start(_,1) ) &
Text.Range( Text.Replace( _, "_", " " ), 1 ) )
You can read more on both functions here:
powerquery.how/text-upper/
powerquery.how/text-range/
Cheers!
@@BIGorilla Thanks!!!!
Hi Rick , I need some help number formatting , i thought you can help me out with it.
Hi shiv, what exactly are you looking for?
Hi sir, I am getting an error :
We expected a Renameoperations value
Details: List
Kindly help
𝐩𝓻Ỗ𝓂Ø𝓈Ｍ 😄
Great video, really enjoyed playing around with it and using it to promote headers (Goodly) etc.
One thing I found was if you got your zipped old and new names as a step; you could then just use;
Table.RenameColumns( Source , newN )
, wher newM = List.Zip( {zipup [Custom] , zipup[Custom.1] } ),
One last puzzle, not directly related, is about TEXT.COMBINE, if I use Add custom column i needed
to convert to text so :
Text.Combine(
List.Transform( [Column1] , each Text.From(_) ), " " )) but if i just used the the formula bar
List.Transform( #"Reordered Columns"[Column1] , (_)=> Text.Combine(_, " " )),
I no longer needed the Text.From(_) , ?
I am using you tutorial and followed the steps. the difference is in data the change column names starts after removing top rows & Promoted Headers steps in Power query. Here is what I have so far: Table.RenameColumns(#"Promoted Headers",List.Zip({Table.ColumnNames(#"Back to Promoted Headers"),#"Transformed Columns"})). I am getting Error: Expression.Error: The name 'Back to Promoted Heaers' wasn't recognized. Make sure it's spelled correctly. It is spelled correctly. I have tried removed the # and the double quotes to no avail. Hope you can help.