Forum Discussion
Changing the format of column headers
I have a dataset that has column headers that refer to the FY with the data in each of the columns being dollar amounts, similar to below:
| Item | FY 2020-21 | FY 2021-22 | FY 2022-23 | FY 2023-24 | FY 2024-25 | FY 2025-26 |
| Salary | 20 | 22 | 25 | 27 | 30 | 33 |
| Utilities | 5 | 6 | 8 | 9 | 11 | 12 |
| Travel | 8 | 10 | 9 | 10 | 12 | 13 |
| Admin | 6 | 6 | 7 | 8 | 9 | 9 |
| Supplies | 15 | 15 | 16 | 16 | 18 | 17 |
It would be ideal to be able to convert the column headers in date formats to provide greater flexibility with setting up visualisation parameters. I started developing a separate date table but couldn't work out the best way to establish the required relationships. Any suggestions on how to best facilitate this?
Hi BJE ,
You need to 'normalise' your table structure.
Select your item field in Power Query then go to Transform tab > Unpivot Columns > Unpivot Other Columns.
This will now give you a single column of your year values on which to perform functions to convert to a recognisable date format, as well as a single column on which to add a relationships.
This table structure will also make it WAAAY simpler to create measures based on your values data.
Pete
2 Replies
Hi BJE ,
You need to 'normalise' your table structure.
Select your item field in Power Query then go to Transform tab > Unpivot Columns > Unpivot Other Columns.
This will now give you a single column of your year values on which to perform functions to convert to a recognisable date format, as well as a single column on which to add a relationships.
This table structure will also make it WAAAY simpler to create measures based on your values data.
Pete
Hi BJE ,
Using below M codes to get the year period for each Item:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY07CoAwEETvsnWKfDAxpWdQq5AioEUgivgDb+9uomIxr5h9zDoHbUhhvYCB5ARJqAgGoahTCjxz0O8xxT2OG1YkaEyNsRghCDJ73RrOMT1HwV+DFwNR5pphivMzQzG/OZuN9liWVP6J6oP+kPcNeH8D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"FY 2020-21" = _t, #"FY 2021-22" = _t, #"FY 2022-23" = _t, #"FY 2023-24" = _t, #"FY 2024-25" = _t, #"FY 2025-26" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"FY 2020-21", Int64.Type}, {"FY 2021-22", Int64.Type}, {"FY 2022-23", Int64.Type}, {"FY 2023-24", Int64.Type}, {"FY 2024-25", Int64.Type}, {"FY 2025-26", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Remove([Attribute],{"F","Y"})), #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Trim, type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each "20"&Text.From([Custom.2])), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Custom.2"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom.2", each List.Range({[Custom.1]..[Custom]},0)), #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.2",{"Custom.1", "Custom"}) in #"Removed Columns1"And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
