Forum Discussion
To Pivot or not to Pivot
kirbynguyen , the second one as a single table is much better.
Follow Up Question:
Is there a way to easily pivot this into the desired output without having to create a lot of extra steps to get rid of duplicate rows:
Date NA Price EU Price FE Price NA Temp EU Temp FE Temp
1/1/2021 2.3 3.3 6.6 10 40 60
1/2/2021 2.6 3.6 6.3 20 50 60
1/3/2021 2.7 3.2 6.9 30 40 60
1/4/2021 2.5 3.6 7.3 10 50 60
1/5/2021 2.2 3.6 7.1 20 40 60
1/6/2021 2.7 3.4 6.5 30 50 60
1/7/2021 2.7 3.6 6.8 10 40 60
1/8/2021 2.4 3.2 7.3 20 50 60
1/9/2021 2.6 3.3 6.9 30 40 60
Desired:
Date Region Region Price Region Temp
1/1/2021 NA 2.3 10
1/1/2021 EU 3.3 40
1/1/2021 FE 6.6 60
1/2/2021 NA 2.6 20
1/2/2021 EU 3.6 50
1/2/2021 FE 6.3 60
The way I am doing it now is that I'll unpivot the columns and then I'll create a calculated column to match the regions and then filter out the one's that don't match. Seems like a lot of extra steps, but I'm not sure if there is a better way to do it
Unpivot all except date, split the attribute column and then pivot on the Temp/Price column.
let
Source = <....>,
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Region", "Column"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Column]), "Column", "Value")
in
#"Pivoted Column"