Split Column with two data types into rows

[Anonymous]

Hi, 

I am a new Power query user so apologies if this is already covered somewhere else.

I have a column with two data types. One is a system code and another is the product description. They are in a table, tabulated in rows, sharing the same order ID and date of order. There are other columns with data.

Example

Date                     Order ID              Code      

01/01/2021          1034                   126u78-15345

01/01/2021          1034                   Gucci handbag black

See screenshot for the actual table

I want to 'group them together so that the Gucci Handbag Black will go into a separate column but in the same row with 126u78-15345 so there's only one row.

Thanks in advance!

[BA_Pete · Accepted solution]

Hi @Anonymous ,

One way to do it would be to merge the table on itself then remove defunct rows:

1) Go to Home tab > Merge Queries

2) In the merge dialog, select your [OrderID] field, then Ctrl+click your [CreatedOnUtc] field. You should see a little 1 and 2 appear on these column headers in the preview.

3) For the second table in the dialog, select the same table again - it will have '(current)' next to it.

4) Select the same columns in the same way in the same order.

5) Select Left Outer as merge type. Hit OK.

6) Expand [OrderGuid] from the nested merged table

7) Create a new filter step something like this:

= Table.SelectRows(previousStep, each [storeId] <> null and [orderGuid] <> [orderGuid.1])

It's a bit of a hack, but quick and easy to explain/implement.

Pete

[Anonymous]

That's perfect.

Thank you Pete!!