Power Query Experts - Need your support please

[Lio123]

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 below

Thanks!!

[v-dineshya]

Hi @Lio123 ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. Created sample data based on your screenshot. Please refer below snap.

2. In Query editor, Click on "New source" --> "Blank Query" and click on "Advanced editor" remove the all code.

Please paste the below M code in "Advanced editor" and click "OK". 

let

Source = #table(
{"Country", "City", "Jan Payment", "Jan Profit", "Jan Loss", "Feb Payment", "Feb Profit", "Feb Loss", "Mar Payment", "Mar Profit", "Mar Loss"},
{
{"X", "A", 100, 5000, 0, 200, 200, 100, 150, 350, 10}
}
),

Unpivoted = Table.UnpivotOtherColumns(Source, {"Country", "City"}, "Attribute", "Value"),

SplitAttribute = Table.SplitColumn(
Unpivoted,
"Attribute",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"Month", "Metric"}
),

Pivoted = Table.Pivot(
SplitAttribute,
List.Distinct(SplitAttribute[Metric]),
"Metric",
"Value"
),

Reordered = Table.SelectColumns(Pivoted, {"Country", "City", "Month", "Payment", "Profit", "Loss"})
in
Reordered

3. Please refer output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @Lio123 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[jgeddes · Accepted solution]

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"