To Pivot or not to Pivot

[kirbynguyen]

Hello,

I am wondering what is more efficient for Power BI.

I have a table that has one row for every date and each column is a numerical value. Some columns are just price columns and some columns are region specific. This is just an example and there are going to be more columns than just the region price that are region specific, such as volume, temperature, etc.

Date	Price	NA Price	EU Price	FE Price
1/1/2021	1.5	2.3	3.3	6.6
1/2/2021	1.4	2.6	3.6	6.3
1/3/2021	1.6	2.7	3.2	6.9
1/4/2021	1.4	2.5	3.6	7.3
1/5/2021	1.5	2.2	3.6	7.1
1/6/2021	1.6	2.7	3.4	6.5
1/7/2021	1.6	2.7	3.6	6.8
1/8/2021	1.6	2.4	3.2	7.3
1/9/2021	1.4	2.6	3.3	6.9

Would it be better to pivot the data so there is a date column, a region column and the price column? Doing it this way creates more rows, but reduces the amount of columns. Like this:

Date	Region	Region Price	Price
1/1/2021	NA	2.3	1.5
1/1/2021	EU	3.3	1.5
1/1/2021	FE	6.6	1.5
1/2/2021	NA	2.6	1.4
1/2/2021	EU	3.6	1.4
1/2/2021	FE	6.3	1.4

Or how about if I create 3 separate calculated tables for each region instead? So each calculated table will be just 2 columns, with the date and the price for that region.

Which would be the best data model from these, or is there an even better way that I didn't mention?

[AlexisOlson]

I'd recommend the following version:

Date	Region	Price
1/1/2021	NA	2.3
1/1/2021	EU	3.3
1/1/2021	FE	6.6
1/1/2021	None	1.5
1/2/2021	NA	2.6
1/2/2021	EU	3.6
1/2/2021	FE	6.3
1/2/2021	None	1.4

[amitchandak · Accepted solution]

@kirbynguyen , the second one as a single table is much better.

[kirbynguyen]

@amitchandak 

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

[AlexisOlson]

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"