Power Query Table Transformation

0
votes

I am using power query in Excel. I have following table generated from a raw SQL query.

ABC_MONTH ABC_YEAR XYZ_MONTH XYZ_YEAR
10 20 30 40

I would like to transform the above table into the following one in power query or tansformations. Kindly help.

NAME MONTH YEAR
ABC 10 20
XYZ 30 40
1
powerbipowerquery

1 Answers

1
votes

First unpivot all the columns to get this:

enter image description here

Split the Attribute column by the left-most underscore:

enter image description here

To get this:

enter image description here

Then pivot Attribute.2 and Value:

enter image description here

To get the desired output (after renaming Attribute.1 to NAME):

enter image description here

The M code to try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyAhHGIMLEQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ABC_MONTH = _t, ABC_YEAR = _t, XYZ_MONTH = _t, XYZ_YEAR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ABC_MONTH", Int64.Type}, {"ABC_YEAR", Int64.Type}, {"XYZ_MONTH", Int64.Type}, {"XYZ_YEAR", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "NAME"}})
in
    #"Renamed Columns"