cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BJE
Helper I
Helper I

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:

 

ItemFY 2020-21FY 2021-22FY 2022-23FY 2023-24FY 2024-25FY 2025-26
Salary202225273033
Utilities56891112
Travel8109101213
Admin667899
Supplies151516161817

 

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?

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1632731610310.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

BA_Pete
Super User
Super User

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors