Forum Discussion

Tomcom05's avatar
Tomcom05
New Member
7 years ago
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 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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

9 Replies

  • MarkLaf's avatar
    MarkLaf
    Super User
    7 years ago

    I think you have to use some M code to get what you want. The below code changes this:

    transactioncustomerline_IDline_descriptionline__price
    8606Cust13966Micro Sheet$11.99
    1033Cust23630Dress$20.00
    1033Cust26905Brand Blouse Size M$7.00
    1033Cust22046White Blazer Size 14$7.00
    9474Cust31274Old Fashioned Scale$26.00
    9474Cust32001Toothbrush$3.87

     

    to this (which I think is what you want):

    transactioncustomer line_ID_1line_description_1line__price_1line_ID_2line_description_2line__price_2line_ID_3line_description_3line__price_3
    8606Cust13966Micro Sheet$11.99nullnullnullnullnullnull
    1033Cust23630Dress$20.006905Brand Blouse Size M$7.002046White Blazer Size 14$7.00
    9474Cust31274Old Fashioned Scale$26.002001Toothbrush$3.87nullnullnull

     

    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's avatar
    hthota
    Resolver III
    7 years ago

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

    • Tomcom05's avatar
      Tomcom05
      New Member
      7 years ago

      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's avatar
        Ashish_Mathur
        Super User
        7 years ago

        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.