Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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"
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
29 | |
3 | |
3 | |
2 | |
2 |