Forum Discussion
Lio123
Helper II
1 year agoPower Query Experts - Need your support please
Dear All, I need your help in transforming the data in Power query. Please let me know how to achieve this. Table I have is : And the format I want to convert the above table like belo...
- 1 year ago
You can select 'Country' and 'City' columns and choose 'Unpivot other columns' on the Transform ribbon.
Make sure the value column is a number.
Split the 'Attribute' column by delimiter (space).
Pivot on the 'Attribute.2' column, make sure aggregation is set to Sum on the value column.
Example code:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUXIEYkMDAyBpagCmQNjIAEFCJA1NQaSxKUREKVYnWikSyHSCcEGaQQRYE4wwhOgDawOxlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, City = _t, #"Jan Payment" = _t, #"Jan Profit" = _t, #"Jan Loss" = _t, #"Feb Payment" = _t, #"Feb Profit" = _t, #"Feb Loss" = _t, #"Mar Payment" = _t, #"Mar Profit" = _t, #"Mar Loss" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Country", "City"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Month", "Item"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Item]), "Item", "Value", List.Sum) in #"Pivoted Column"
jgeddes
Super User
1 year agoYou can select 'Country' and 'City' columns and choose 'Unpivot other columns' on the Transform ribbon.
Make sure the value column is a number.
Split the 'Attribute' column by delimiter (space).
Pivot on the 'Attribute.2' column, make sure aggregation is set to Sum on the value column.
Example code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUXIEYkMDAyBpagCmQNjIAEFCJA1NQaSxKUREKVYnWikSyHSCcEGaQQRYE4wwhOgDawOxlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, City = _t, #"Jan Payment" = _t, #"Jan Profit" = _t, #"Jan Loss" = _t, #"Feb Payment" = _t, #"Feb Profit" = _t, #"Feb Loss" = _t, #"Mar Payment" = _t, #"Mar Profit" = _t, #"Mar Loss" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Country", "City"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Month", "Item"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Item]), "Item", "Value", List.Sum)
in
#"Pivoted Column"