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.
Dear community,
Could you please help with hint on M code to do the following.
I have several columns in table, with headers similar to this:
abc[some good text]xyz.
I need to rename all column headers from these
abc[some good text]xyz
to
some good text
In other words, leave only the text between [ ] and remove everything else.
I figured out, that I will most probably need Table.Renamecolumns and Text.Betweendelimeters functions - but I can't combine it into the working M code, exceeds my current capabilities...
Solved! Go to Solution.
Actually, Table.TransformColumnNames is the better choice.
Example (assuming if "[" is present, the "]" will also be there):
let Source = Table1, RenamedColumns = Table.TransformColumnNames(Source, each if Text.Contains(_,"[") then Text.BetweenDelimiters(_,"[","]") else _) in RenamedColumns
@vyacheslavg - you could perhaps try with the transpose feature that will switch your columns to rows (and rows to columns) and then make one (or more) search/replace on the first column (that used to be your column headers) and after you make your search/replace you transpose the table again to put columns back as columns.
Actually, Table.TransformColumnNames is the better choice.
Example (assuming if "[" is present, the "]" will also be there):
let Source = Table1, RenamedColumns = Table.TransformColumnNames(Source, each if Text.Contains(_,"[") then Text.BetweenDelimiters(_,"[","]") else _) in RenamedColumns
Marcel, thanks a lot.
It looks very close to a solution (I need to test it first).
Edited:
It worked!
Spent some time trying to figure out strange results - I forgot to promote headers first. After that everything worked just great.
Could you please advise some good training or book for Power Query/M?
I've found the "M for the Data Monkey", but it seems to be so heavy PowerPivot related... Want to find something more PBD and SSDT focused.
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |