topic Re: Power Query Experts - Need your support please in Power Query

Power Query Experts - Need your support please

Lio123 — Wed, 16 Jul 2025 16:13:01 GMT

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!!

Re: Power Query Experts - Need your support please

jgeddes — Wed, 16 Jul 2025 17:25:24 GMT

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"

Re: Power Query Experts - Need your support please

v-dineshya — Thu, 17 Jul 2025 07:37:24 GMT

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

Re: Power Query Experts - Need your support please

v-dineshya — Mon, 21 Jul 2025 15:36:38 GMT

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