Forum Discussion
Solved
Pivot Multiple Columns in Power BI
Hi everyone
i need to pivot this table in power bi and got this result :
the original table is a input file : Excel
thanks in advance
Please follow steps below:
1. Unpivot all values columns.
2. Pivot the date column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDNCoAwCIDfZecCf+Zq5zoFQffR+79GapcKKwbq5FO+rbW0IkDq0rxNqImAsNdjLc6iEYtkTZD27g22OrPD5wzp5YYvV9zKWjTWMtokwAdsLSlOM//SbuJbn9oYv1FkDLUD3HdjjbwDmt69A9oIZjk/xZUIBh3YDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code article" = _t, Division = _t, Date = _t, Consommation = _t, #"Couverture 1%" = _t, #"Livraison Prevue" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code article", type text}, {"Division", type text}, {"Date", type date}, {"Consommation", Int64.Type}, {"Couverture 1%", Int64.Type}, {"Livraison Prevue", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Division", "Code article"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type text}}, "en-US")[Date]), "Date", "Value", List.Sum) in #"Pivoted Column"
2 Replies
Please follow steps below:
1. Unpivot all values columns.
2. Pivot the date column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDNCoAwCIDfZecCf+Zq5zoFQffR+79GapcKKwbq5FO+rbW0IkDq0rxNqImAsNdjLc6iEYtkTZD27g22OrPD5wzp5YYvV9zKWjTWMtokwAdsLSlOM//SbuJbn9oYv1FkDLUD3HdjjbwDmt69A9oIZjk/xZUIBh3YDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code article" = _t, Division = _t, Date = _t, Consommation = _t, #"Couverture 1%" = _t, #"Livraison Prevue" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code article", type text}, {"Division", type text}, {"Date", type date}, {"Consommation", Int64.Type}, {"Couverture 1%", Int64.Type}, {"Livraison Prevue", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Division", "Code article"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type text}}, "en-US")[Date]), "Date", "Value", List.Sum) in #"Pivoted Column"thank u so much
