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.
Hi everyone,
I am trying to format some imported column headers to my desired output (we are migrating from Dynamics AX2012 to Finance and Operations).
I imported a maintenance table where all the column headers were all in uppercase letters. First step I did was to Capitalize all column headers, thanks to this query in M (advanced editor within Power Query):
let
Source = Sql.Database("secret.database.windows.net", "AxDW", [Query="SELECT * FROM MaintenanceTable"]),
#"Renamed Columns" = Table.TransformColumnNames(Source, Text.Proper)
in
#"Renamed Columns"
Now I want to rename each column header that contains the word "key" (this will always be at the end of a header name), to the same name, but with a '%' sign inserted at the start of that name. If the word "key" was not found, just leave the column name as it is.
Anyone who knows the solution? Thanks in advance!
Solved! Go to Solution.
Hi @Tamerlane
Try below. Custom1 will cater for all combination like Key,key etc. I have changed order for Text.Proper as we need it after Custom1.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmcgdlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [account = _t, Name = _t, PrimaryKey = _t, ProductKey = _t]),
Custom1 = Table.TransformColumnNames(Source, each if Text.Contains(Text.Upper(_),"KEY") then "%" & Text.Replace(Text.Upper(_),"KEY","") else _),
Custom2 = Table.TransformColumnNames(Custom1,Text.Proper)
in
Custom2
Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.
Hi @Tamerlane ,
Thanks both, it works great!
Hi @Tamerlane
Try below. Custom1 will cater for all combination like Key,key etc. I have changed order for Text.Proper as we need it after Custom1.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmcgdlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [account = _t, Name = _t, PrimaryKey = _t, ProductKey = _t]),
Custom1 = Table.TransformColumnNames(Source, each if Text.Contains(Text.Upper(_),"KEY") then "%" & Text.Replace(Text.Upper(_),"KEY","") else _),
Custom2 = Table.TransformColumnNames(Custom1,Text.Proper)
in
Custom2
Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |