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))))
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 🙏
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?
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
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!
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, ....}
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)
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?
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!🙏
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??
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?
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.
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(_) , ?
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! Used this to help rename a column to a new name if it contained a word in the original name.
Thank you very much from Thailand. I really like these technics.
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))))
Amazing vid. The Table.TransformColumnNames fx was a bomb.
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.
Bedankt Rick! Echt top dat je deze video hebt gemaakt. Op dik 60 kolommen scheelt me dit een hoop tijd ;)
This is brilliant and will save me so much time. Thank you. :)
And as an added bonus it all folds.
awesome. definatly need to invest more time in Power Query as there is so much to gain
excellent indeed! but a little complex.
Clever solution! Thx
Glad you like it 👏
Great information! Thank you
Glad it was helpful!
Amazing video. Little complex but you explained it very well. Thanks for sharing. You got a subscriber 😊
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?
Great video. Thanks for that.
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
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!!!!
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, ....}
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)
Great Video Rick
Thanks Johan 🙏
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!🙏
Very nice tutorial 👍
This is next level. Awesome thanks
Well explained. Thanks for sharing.
Thank you. This is very useful and pretty cool solutions👍
Excellent video and explanation. Thank you.
You are welcome!
Thank you very much.These Tipp are very helpful
Welcome 😊
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??
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?
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!
Brilliant 👍
Superb!
Great trick!
Fantastic Rick
14.45
Hi sir, I am getting an error :
We expected a Renameoperations value
Details: List
Kindly help
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?
Just amazing!!!
Wow wow, thanks for the kind words !! 🚀😁
Amazing
𝐩𝓻Ỗ𝓂Ø𝓈Ｍ 😄
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.
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(_) , ?