Forum Discussion
Solved
Combining rows by order
I have data from our sales system that I am trying to prepare for an API connection. The orders are split into multiple rows for item detail and I am looking for a way to get the orders & items combi...
Hi,
Given the Table shared by MarkLaf, this M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"transaction", type text}, {"customer", type text}, {"line_ID", Int64.Type}, {"line_description", type text}, {"line__price", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"transaction", "customer"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"transaction", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"customer", "Value", "Index"}, {"customer", "Value", "Index"}), #"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",{{"Merged.1", Order.Ascending}, {"Index", Order.Ascending}, {"Merged.2", Order.Ascending}}), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value") in #"Pivoted Column"Hope this helps.
I think you have to use some M code to get what you want. The below code changes this:
| transaction | customer | line_ID | line_description | line__price |
| 8606 | Cust1 | 3966 | Micro Sheet | $11.99 |
| 1033 | Cust2 | 3630 | Dress | $20.00 |
| 1033 | Cust2 | 6905 | Brand Blouse Size M | $7.00 |
| 1033 | Cust2 | 2046 | White Blazer Size 14 | $7.00 |
| 9474 | Cust3 | 1274 | Old Fashioned Scale | $26.00 |
| 9474 | Cust3 | 2001 | Toothbrush | $3.87 |
to this (which I think is what you want):
| transaction | customer | line_ID_1 | line_description_1 | line__price_1 | line_ID_2 | line_description_2 | line__price_2 | line_ID_3 | line_description_3 | line__price_3 |
| 8606 | Cust1 | 3966 | Micro Sheet | $11.99 | null | null | null | null | null | null |
| 1033 | Cust2 | 3630 | Dress | $20.00 | 6905 | Brand Blouse Size M | $7.00 | 2046 | White Blazer Size 14 | $7.00 |
| 9474 | Cust3 | 1274 | Old Fashioned Scale | $26.00 | 2001 | Toothbrush | $3.87 | null | null | null |
Note that to get this working for your data, you'll have to change Source to point to your table, and then you'll have to replace relevant column names with your own (noted with comments in code):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZA9C4MwFEX/SggdRV4+Gs1qSzfpYKGDOKT1QQQxkOjir29iXQp1ulw4By63bWmpQNGMXpYws5hCq1Tr4e0daSziHNuJsVxr2mUtZSDEjvOEKwExrh5DSCCHHOAfqDScY1TeTD2pRrcEJM2wIqmTVhxYHGRa87TDjNEyK/qvxeSPpmUhdy3pjG/1PvbkZoId3IQ9ad5mxG2jOtA4QLrg4dxsX34JNtEiLwvadR8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [transaction = _t, customer = _t, line_ID = _t, line_description = _t, line__price = _t]),
GroupTrans = Table.Group(
Source,
{"transaction", "customer"}, //enter all header-level columns here
{{"transactions", each _}}
),
FixTrans = Table.TransformColumns(GroupTrans,{"transactions", each
let
AddIndex = Table.AddIndexColumn(_, "Index", 1, 1),
Unpivot = Table.Unpivot(
AddIndex,
{"line__price", "line_description", "line_ID"}, //enter all line-level columns here
"Attribute",
"Value"
),
AppendIndex = Table.ReplaceValue(Unpivot,each [Attribute],each [Attribute]&"_"&Text.From([Index]),Replacer.ReplaceText,{"Attribute"}),
SelectCols = Table.SelectColumns(AppendIndex,{"Value", "Attribute"})
in
Table.Pivot(SelectCols, List.Distinct(SelectCols[Attribute]), "Attribute", "Value")
}),
AllCols = List.Distinct(List.Accumulate(FixTrans[transactions],{},(state,current)=> state & Table.ColumnNames(current))),
Expand = Table.ExpandTableColumn(FixTrans, "transactions", AllCols)
in
Expand