How to transform data in power query

[PBI_newuser]

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

[amitchandak · Accepted solution]

@PBI_newuser , 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"