Combine rows with similar information into 1

April 25, 2022

I have a table that looks like this example:

Order      Bagged        Shipped
----------------------------------
1             Y    
2                           Y
1                           Y
3             Y

I want to combine like order numbers into 1 row like below:

Order      Bagged        Shipped
----------------------------------
1             Y             Y
2                           Y
3             Y

How can I do this in PowerBi desktop?

>Solution :

Assuming your data really is as simple as your example (values are either null or ‘Y’ and no conflicts), I suggest something like:

SELECT Order, MAX(Bagged), MAX(Shipped)
FROM mytable
GROUP BY Order

The GROUP BY Order indicates you want one row per order, the MAX for the other columns ensures you get the ‘Y’ (if it exists for that Order) or null (if ‘Y’ doesn’t exist for that Order).