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 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?
Solved! Go to Solution.
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
Proud to be a Datanaut!
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,
Kelly
Did I answer your question? Mark my reply as a solution!
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
Proud to be a Datanaut!
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.