merge related rows

[melina]

Hi All,

It is possible to merge rows as below in PBI ?

[MarcelBeug]

This can be done using "Group By" in Power Query (a.k.a. "M", a.k.a. the Query Editor).

However, you should consider all possible cases and your specific requirements: on which fields do you want to group, what must be done with the data in the other fields, e.g can you have multiple lines with different container numbers, should these all be concatenated, e.g. comma separated?

[melina]

Thanks for your reply.

I want to group all the column, including container number. The rest data just remain the value. One SO number only for 1 container number, but the problem is there is certain container number is blank. So i want to merge, those same SO number with the container number by remaining other value in column. At the end of result, only have 1 SO number,not duplicate like picture above.

[MarcelBeug · Accepted solution]

The code below should do what you are looking for:

let
    Source = BeforeMerge,
    #"Grouped Rows" = 
        Table.Group(Source,
                    {"SO NUMBER", "Cust Name", "Order Qty", "DO Qty"},
                    {{"Container Number", each Text.Combine(List.Select([Container Number], each _ <> null and _ <> ""),", "), type text}})
in
    #"Grouped Rows"

This is the result before and after.

I added an example with 3 different container numbers for 1 order, just to clarify my remarks in the previous post.

In general: when you want to group a table, you have 2 kinds of fields: the ones to group on, meaning you'll end up with the unique combinations in these fields, i.c. the first 4 fields.

You can not group on all 5 fields, otherwise you'll end up with the initial table, as a blank container number is something else as a container number.

For the other fields in your table, you need to specify how these must be aggregated, i.c. the Container Number.

The query code above will combine all not-empty container numbers, separated by ", ".

If you have any other colums in your table, then you should specify what to do with these as well (or they will just drop off).

[melina]

Thanks a lot! Your explanation help me to solve my problem.