Forum Discussion
Power Query Merge - take only first match
Hi folks,
I'm looking for a way to perform a merge query in Power Query, but only take the first match if it finds multiple. Below are examples of the data I have to work with and what my end goal is. Any thoughts would be appreciated,
Left Table:
| Item Number | Date |
| A123 | 2022-01-01 |
| B234 | 2022-01-02 |
| C345 | 2022-01-02 |
Right Table:
| Item Number | Date | ID |
| A123 | 2022-01-01 | ZZZ |
| C345 | 2022-01-02 | XXX |
| C345 | 2022-01-02 | YYY |
Result I'm getting with Left-Outer Merge
| Item Number | Date | ID |
| A123 | 2022-01-01 | ZZZ |
| B234 | 2022-01-02 | |
| C345 | 2022-01-02 | XXX |
| C345 | 2022-01-02 | YYY |
My goal result:
| Item Number | Date | ID |
| A123 | 2022-01-01 | ZZZ |
| B234 | 2022-01-02 | |
| C345 | 2022-01-02 | XXX |
Hi kevinsa ,
I suggest you to add an [Index] column to sort your ID by [Item Number] and [Date] group in Right Table.
For reference:
Create Row Number for Each Group in Power BI using Power Query
After you merge two table by left out, you can expand [ID] and [Index] in Right Table. Add a filter on new table to only keep the index value = null or 1.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Select the Item Number column and remove duplicates after merging them using full outer join.
Hi kevinsa ,
I suggest you to add an [Index] column to sort your ID by [Item Number] and [Date] group in Right Table.
For reference:
Create Row Number for Each Group in Power BI using Power Query
After you merge two table by left out, you can expand [ID] and [Index] in Right Table. Add a filter on new table to only keep the index value = null or 1.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Ashish_Mathur I'd love to download your method for this, but the download link isnt working for me.
Hi,
I do not have that file now. Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
