Search code examples
exceldatatablespowerbidaxformula

Convert Complex Table to Simple Table

I have the below table:

enter image description here

I need my table to be formatted in either Excel or PowerBi to look like the below:

enter image description here

Basically, I need the date to become a column.

I am unsure where to start with this. Please let me know if I need to provide any more information.

Solution

  • The PowerQuery would look something like this:

    let
Source = Excel.Workbook(File.Contents("C:\Users\User\Desktop\UnpivotMe.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Location", type text}, {"1/1/2024", type number}, {"1/2/2024", type number}, {"1/3/2024", type number}, {"1/4/2024", type number}, {"1/5/2024", type number}, {"1/6/2024", type number}, {"1/7/2024", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Location] <> null)),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Location"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}})

    in #"Renamed Columns"

    The super short answer is to select the Location column and then select Unpivot Other Columns, and then you may have to change data types and that's pretty much it. (That "Filtered Rows" step was there because I selected too many columns, so I got nulls all over the place that I didn't want.) For some odd reason, I could only get it to work if I created a new workbook and then used Get Data, Excel... to read the other file. (I must have done something dumb.)