Forum Discussion
abderrazak
4 years agoFrequent Visitor
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
- 4 years ago
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"
V-lianl-msft
4 years agoCommunity Support
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"- abderrazak4 years agoFrequent Visitor
thank u so much