March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Solved! Go to Solution.
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 Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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 Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Select the Item Number column and remove duplicates after merging them using full outer join.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |