Forum Discussion

Lio123's avatar
Lio123
Icon for Helper II rankHelper II
1 year ago
Solved

Power 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...
  • jgeddes's avatar
    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"