Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table imported into Power Query editor. The columns have the following names:
ID | Agg | Cluster1 | Cluster2 | Cluster3...
------------------------------------------
1234 | 0.232 | abdef | wdkdf | dksdmsd
1092 | 0.436 | hghg | kjfgfkf | opwkffkd
1342 | 1.299 | wlslvs | isivmw | wyywcga
I am trying to rename the "Cluster" columns with "TC" but changing their Column number to it's corresponding Letter. e.g. :
ID | Agg | TC A | TC B | TC C ...
---------------------------------
= Table.TransformColumnNames(Table1, each if Text.Contains(_,"Cluster") then Text.Replace(_,"Cluster","TC " & Character.FromNumber(Number.FromText(Text.Middle(Text.Replace(_,"Cluster","TC "), 2))+64)) else _ )
I was able to write the above Query, however, i am getting the below output:
ID | Agg | TC A1 | TC B2 | TC C3 ...
------------------------------------------
1234 | 0.232 | abdef | wdkdf | dksdmsd
1092 | 0.436 | hghg | kjfgfkf | opwkffkd
1342 | 1.299 | wlslvs | isivmw | wyywcga
I was able to rename correctly using List, but don't know how to plug this List as Header row in my original table:
= List.Transform(List.ReplaceValue(List.FindText(Table.ColumnNames(Table1),""),"Cluster","TC",Replacer.ReplaceText), each if Text.Contains(_,"TC") then Text.Replace("_", "_","TC " & Character.FromNumber(Number.FromText(Text.Middle(_,2))+64)) else _ )
ID | Agg | TC A1 | TC B2 | TC C3 ...
Any idea why i am getting this output and how do i rename them correctly?
Solved! Go to Solution.
Hi @Anonymous ,
your formula won't work in a "Table.TransformColumnNames"-formula. You have to use Table.RenameColumns instead like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Agg = _t, Cluster1 = _t, Cluster2 = _t]), Custom1 = Table.RenameColumns( Source, List.Zip( { Table.ColumnNames(Source), List.Transform( List.ReplaceValue( List.FindText( Table.ColumnNames(Source), ""), "Cluster", "TC", Replacer.ReplaceText), each if Text.Contains(_,"TC") then Text.Replace("_", "_","TC " & Character.FromNumber(Number.FromText(Text.Middle(_,2))+64)) else _ ) } )) in Custom1
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous ,
Check out this blog from @MattAllington at Renaming all the columns at once. He is also having a free webinar next week. Check it out!
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @Nathaniel_C ,
It doesn't work for me. I need to also convert the column numbers to their corresponding Letters.
Hi @Anonymous ,
Maybe @ImkeF can help. She is major league for M.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
your formula won't work in a "Table.TransformColumnNames"-formula. You have to use Table.RenameColumns instead like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Agg = _t, Cluster1 = _t, Cluster2 = _t]), Custom1 = Table.RenameColumns( Source, List.Zip( { Table.ColumnNames(Source), List.Transform( List.ReplaceValue( List.FindText( Table.ColumnNames(Source), ""), "Cluster", "TC", Replacer.ReplaceText), each if Text.Contains(_,"TC") then Text.Replace("_", "_","TC " & Character.FromNumber(Number.FromText(Text.Middle(_,2))+64)) else _ ) } )) in Custom1
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |