Combining rows by order

[Tomcom05]

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 combined onto one line. I am fairly new to Power BI, but I feel like its something it can do. Any help would be great!

I have tried using group by with something similar to the following command, but it keeps giving errors about the wrong type of data.

#"Grouped Rows" = Table.Group(Source, {"transaction_id"}, {{"Items Detail", each Text.Combine({[line_ID],[line_description],[line__price], ", "), type text}})

Below are some examples of the way the data sits now and then below that is what we need it to look like.

Raw Data Example:

Fixed Data Example:

I have looked for someone who has the same issue without any luck, but if there is a thread that matches this issue please let me know.

-Tom

[MarkLaf]

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

[hthota]

Could you please highlight the difference between the two images. Which column do you need to combine.

[Tomcom05]

Its a combination of the second order. See red box for the new columns. 

---

@hthota wrote:

Could you please highlight the difference between the two images. Which column do you need to combine.

---

[Ashish_Mathur · Accepted solution]

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.

[Tomcom05]

Thanks everyone! This is what i needed to get this project finished.

-Tom

[Ashish_Mathur]

You are welcome.

[hthota]

Create a Calculated Coulmn aas given below.

Column= TableName[line2_ID]&" "&TableName[line2_Des]&" "&TableName[line2_price]&" "&TableName[line3_ID]&" "&TableName[line2_Des]&" "&TableName[line2_price]

[Tomcom05]

---

@hthota wrote:

Create a Calculated Coulmn aas given below.

Column= TableName[line2_ID]&" "&TableName[line2_Des]&" "&TableName[line2_price]&" "&TableName[line3_ID]&" "&TableName[line2_Des]&" "&TableName[line2_price]

---

I am trying to get a combined row per order with each order having an unknown number of lines. The [line#_id] don't exist yet so the above calculation gives me an error. Am I missing something?

The only similar posts I found on this are below.

https://community.powerbi.com/t5/Desktop/Combine-multiple-rows-of-User-data-into-one-row-with-additi... 

https://community.powerbi.com/t5/Desktop/Combine-values-of-multiple-rows-in-one-row/td-p/308919

[hthota]

I think it would work. Orelse please add two coulmns, later three etc in same coulmn and do testing to figure the effected column