Combine columns into one line for each order #

[Anonymous]

Hi, I am looking for help on how to combine all the process order status names for each OCP # into the same line. Using the latest transaction date. If anyone has any suggestions I would greatly appreciate it. 

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

1.Please try it in power query (group by).

= Table.Group(#"Changed Type", {"OCP#"}, {{"Ship Date", each List.Max([Ship Date]), type nullable date}, {"TransactionDateTime", each List.Max([TransactionDateTime]), type nullable datetime}, {"a", each Text.Combine([ProcessOrderStatusName], ", "), type nullable text}})

The final output is shown below:

2.If i understand error, you could try another like below:

Here is M language.

= Table.Group(#"Changed Type", {"OCP#", "ProcessOrderStatusName"}, {{"Ship Date", each List.Max([Ship Date]), type nullable date}, {"TransactionDateTime", each List.Max([TransactionDateTime]), type nullable datetime}})

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

On a slightly larger datase, show the expected result.  I'd like to see what you specifically mean by the bold underlines portion - "Combine all the process order status names for each OCP # into the same line. Using the latest transaction date."?

[smpa01]

@Anonymous  not entirely sure, what your requirement is, but if you have table like following

| ocp | datetime             | status | shipDate   | Index |
|-----|----------------------|--------|------------|-------|
| s1  | 1/1/2021 12:46:00 PM | a      | 2021-01-01 | 1     |
| s1  | 1/1/2021 1:46:00 PM  | b      | 2021-01-01 | 2     |
| s1  | 1/1/2021 2:46:00 PM  | c      | 2021-01-01 | 3     |
| s1  | 2/1/2021 2:46:00 PM  | d      | 2021-02-01 | 4     |
| s2  | 4/1/2021 12:46:00 PM | x      | 2021-04-01 | 5     |
| s2  | 5/1/2021 12:46:00 PM | y      | 2021-05-01 | 6     |
| s3  | 6/1/2021 12:46:00 PM | z      | 2021-06-01 | 7     |

and want an output like following

then you need a measure like following

Measure =
VAR _0 =
    CALCULATE ( MAX ( 'Table'[shipDate] ), ALLEXCEPT ( 'Table', 'Table'[ocp] ) )
VAR _1 =
    CALCULATE (
        MAXX (
            FILTER (
                ADDCOLUMNS (
                    'Table',
                    "@concat",
                        CALCULATE (
                            CONCATENATEX ( 'Table', 'Table'[status], "," ),
                            ALLEXCEPT ( 'Table', 'Table'[ocp] )
                        )
                ),
                [shipDate] = _0
            ),
            [@concat]
        ),
        ALLEXCEPT ( 'Table', 'Table'[ocp] )
    )
RETURN
    _1

[Anonymous]

#"Grouped Rows" = Table.Group(Source, vDbo_vActiveOrderDashBoardDetails, {"ProcessOrderNbr"}, {{"Order Status", each Text.Combine([PhaseStatusValue],", "), type text}})
   
in
    #"Grouped Rows"

I tried this code but I am getting this error: