Forum Discussion
How to transform data in power query
Hi, I have an excel file as below. How to transform the data to the expected result?
| Campaign | Name | Product | Amt | Product | Amt | Product | Amt |
| A | B | P-1 | 123 | P-3 | 321 | P-5 | 456 |
| C | D | P-2 | 444 | P-4 | 444 | P-6 | 666 |
Expected result:
| Campaign | Name | Product | Amt |
| A | B | P-1 | 123 |
| A | B | P-3 | 321 |
| A | B | P-5 | 456 |
| C | D | P-2 | 444 |
| C | D | P-4 | 444 |
| C | D | P-6 | 666 |
Anonymous , Add this code in blank Query in power query and check
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIC4gBdQyBpaGQMZoNIYyNDMNsUSJqYminF6kQrOQPZLmBRI5CoiQmYbYLENgOSZmZA1bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Campaign = _t, Name = _t, Product = _t, Amt = _t, Product.1 = _t, Amt.1 = _t, Product.2 = _t, Amt.2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign", type text}, {"Name", type text}, {"Product", type text}, {"Amt", Int64.Type}, {"Product.1", type text}, {"Amt.1", Int64.Type}, {"Product.2", type text}, {"Amt.2", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Campaign"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"
1 Reply
Anonymous , Add this code in blank Query in power query and check
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIC4gBdQyBpaGQMZoNIYyNDMNsUSJqYminF6kQrOQPZLmBRI5CoiQmYbYLENgOSZmZA1bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Campaign = _t, Name = _t, Product = _t, Amt = _t, Product.1 = _t, Amt.1 = _t, Product.2 = _t, Amt.2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign", type text}, {"Name", type text}, {"Product", type text}, {"Amt", Int64.Type}, {"Product.1", type text}, {"Amt.1", Int64.Type}, {"Product.2", type text}, {"Amt.2", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Campaign"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"