Forum Discussion
Transform data - Unpivot and Pivot Column
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.
Super UserAnonymous , 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"
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 |