Transform data - Unpivot and Pivot Column

[PBI_newuser]

Hi, I try to transform the below data to the expected result but failed. Please help.

Campaign Name

Name

Product

Qty

Price

Product.1

Qty.1

Price.1

Product.2

Qty.2

Price.2

Product.3

Qty.3

Price.3

ABC

Bobo

Apple

1

16.00

Orange

1

25.80

Grape

2

20.00

Banana

1

36.45

ABC

Bobo

Apple

6

96.00

Orange

7

180.60

Grape

8

80.00

Banana

8

291.60

Expected Result:

Campaign Name	Name	Product	Qty	Price
ABC	Bobo	Apple	1	16.00
ABC	Bobo	Orange	1	25.80
ABC	Bobo	Grape	2	20.00
ABC	Bobo	Banana	1	36.45
ABC	Bobo	Apple	6	96.00
ABC	Bobo	Orange	7	180.60
ABC	Bobo	Grape	8	80.00
ABC	Bobo	Banana	8	291.60

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcspPygdSjgUFOalA2hCEzfQMDIC0f1FiXjpM0MhUzwIk6F6UWAASMwJhA4hCp8Q8IIQqNDbTMzFVitXBYYEZEFuiW2AO0mthoGeGbIMFCKPbABI0sjQEqYyNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Campaign Name" = _t, #" Name" = _t, Product = _t, Qty = _t, Price = _t, Product.1 = _t, Qty.1 = _t, Price.1 = _t, Product.2 = _t, Qty.2 = _t, Price.2 = _t, Product.3 = _t, Qty.3 = _t, Price.3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign Name", type text}, {" Name", type text}, {"Product", type text}, {"Qty", Int64.Type}, {"Price", type text}, {"Product.1", type text}, {"Qty.1", Int64.Type}, {"Price.1", type text}, {"Product.2", type text}, {"Qty.2", Int64.Type}, {"Price.2", type text}, {"Product.3", type text}, {"Qty.3", Int64.Type}, {"Price.3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Campaign Name", " Name"}, "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"

[darshaningale]

1) Check the columns that should not change and choose "Unpivot other Columns"

2) Check (the new) colum "Attribute" and Split by delimiter "."

3) Replace null with zero.

4) Check the new column and pivot again. 

Regards
DI

Did I answer your question? Mark my post as a solution, this will help others!
Kudos are also welcome.

[PBI_newuser]

Hi @darshaningale , when i import this excel file, the query is not working. Could you please help to see what's wrong with the query? Sample file here. Thank you.

[v-kkf-msft]

Hi @PBI_newuser ,

Please try the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHBasMwDIZfxeS0QQi2YjvOselgp9KyHcooOWit2Q5pHEJb+viVG7s4Y8UYC/367U/ybpctmmWWZ437dnQIKKXSFKxw3P+yOmfAAdjL1h5eKbsYhs76Ml/x9cGELjineD1i/5MKoArjhfcRB5+HmOeTYdOdj0l5qQupKGb/7KzN/0BqJUsQzyEvbo+He2G4vg6YkaaK+IYXesZvgmLmnDENtZgMT0Gb5ZtH6OyVDhplZeoHqIBI+omndJxlHINU07MN9rT8DUGpFEzKFk92PNrO9YlPajVv8CGo0MeK+nPpPygTHGe8YOIw8u5o2xs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Campaign Name" = _t, Name = _t, #"Contact No" = _t, #"Date Created" = _t, Product = _t, Qty = _t, Price = _t, Product.1 = _t, Qty.1 = _t, Price.1 = _t, Product.2 = _t, Qty.2 = _t, Price.2 = _t, Product.3 = _t, Qty.3 = _t, Price.3 = _t, Product.4 = _t, Qty.4 = _t, Price.4 = _t, Product.5 = _t, Qty.5 = _t, Price.5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign Name", type text}, {"Name", type text}, {"Contact No", Int64.Type}, {"Date Created", type text}, {"Product", type text}, {"Qty", Int64.Type}, {"Price", type text}, {"Product.1", type text}, {"Qty.1", Int64.Type}, {"Price.1", type text}, {"Product.2", type text}, {"Qty.2", Int64.Type}, {"Price.2", type text}, {"Product.3", type text}, {"Qty.3", Int64.Type}, {"Price.3", type text}, {"Product.4", type text}, {"Qty.4", Int64.Type}, {"Price.4", type text}, {"Product.5", type text}, {"Qty.5", Int64.Type}, {"Price.5", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Campaign Name", "Name", "Contact No", "Date Created", "Index"}, "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"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Product] <> " "))
in
    #"Filtered Rows"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi @v-kkf-msft , by importing this excel file and applied the same steps, it doesn't work. The Qty and Price skip one row down as shown in screenshot below. How to fix this?

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign Name", type text}, {" Name", type text}, {" Contact No", Int64.Type}, {" Date Created", type text}, {"Product", type text}, {"Qty", Int64.Type}, {"Price", type text}, {"Product2", type text}, {"Qty3", Int64.Type}, {"Price4", type text}, {"Product5", type text}, {"Qty6", Int64.Type}, {"Price7", type text}, {"Product8", type text}, {"Qty9", Int64.Type}, {"Price10", type text}, {"Product11", type text}, {"Qty12", Int64.Type}, {"Price13", type text}, {"Product14", type text}, {"Qty15", Int64.Type}, {"Price16", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Campaign Name", " Name", " Contact No", " Date Created", "Product", "Qty", "Price", "Product2", "Qty3", "Price4", "Product5", "Qty6", "Price7", "Product8", "Qty9", "Price10", "Product11", "Qty12", "Price13", "Product14", "Qty15", "Price16"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index", "Campaign Name", " Name", " Contact No", " Date Created"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Product", each if [Attribute]="Product" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Product"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Attribute], "Product")),
    #"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows",{"Index", "Campaign Name", " Name", " Contact No", " Date Created", "Product", "Attribute", "Value"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Reordered Columns1", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-IN"),{"Index", "Attribute.1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Added Index1" = Table.AddIndexColumn(#"Merged Columns", "Index", 1, 1, Int64.Type),
    Partition = Table.Group(#"Added Index1", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Campaign Name", " Name", " Contact No", " Date Created", "Product", "Value", "Index", "Index1"}, {"Campaign Name", " Name", " Contact No", " Date Created", "Product", "Value", "Index", "Index1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Merged.1", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"Qty", type number}})
in
    #"Changed Type2"

Hope this helps.

[amitchandak]

@PBI_newuser , Please try. I added index column first

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcspPygdSjgUFOalA2hCEzfQMDIC0f1FiXjpM0MhUzwIk6F6UWAASMwJhA4hCp8Q8IIQqNDbTMzFVitXBYYEZEFuiW2AO0mthoGeGbIMFCKPbABI0sjQEqYyNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Campaign Name" = _t, #" Name" = _t, Product = _t, Qty = _t, Price = _t, Product.1 = _t, Qty.1 = _t, Price.1 = _t, Product.2 = _t, Qty.2 = _t, Price.2 = _t, Product.3 = _t, Qty.3 = _t, Price.3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign Name", type text}, {" Name", type text}, {"Product", type text}, {"Qty", Int64.Type}, {"Price", Int64.Type}, {"Product.1", type text}, {"Qty.1", Int64.Type}, {"Price.1", type number}, {"Product.2", type text}, {"Qty.2", Int64.Type}, {"Price.2", Int64.Type}, {"Product.3", type text}, {"Qty.3", Int64.Type}, {"Price.3", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Campaign Name", " Name"}, "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"

[PBI_newuser]

Hi @amitchandak , when i import this excel file, the query is not working. Could you please help to see what's wrong with the query? Sample file here. 

Excel data:

Campaign Name

Name

Contact No

Date Created

Product

Qty

Price

Product

Qty

Price

Product

Qty

Price

Product

Qty

Price

Product

Qty

Price

Product

Qty

Price

ABC

Bobo

123456

March 9, 2022 (Wed)

Apple

1

MYR 16.00

Orange

1

MYR 25.80

Grape

2

MYR 20.00

Plum

1

MYR 36.45

ABC

Bobo

654321

March 9, 2022 (Wed)

Avocado

6

MYR 96.00

Grape

7

MYR 180.60

Orange

8

MYR 80.00

Plum

8

MYR 291.60

BCD

Alex

456789

March 12, 2022 (Sat)

Apple

3

MYR 345.00

Banana

4

MYR 752.00

Watermelon

3

MYR 465.00

Grape

3

MYR 450.00

Mango

2

MYR 258.00

Guava

3

MYR 840.00