Changing the format of column headers

[BJE]

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?

[v-kelly-msft]

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!

[BA_Pete · Accepted 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