Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kevinsa
Frequent Visitor

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 NumberDate
A1232022-01-01
B2342022-01-02
C3452022-01-02

 

Right Table: 

Item NumberDateID
A1232022-01-01ZZZ
C3452022-01-02XXX
C3452022-01-02

YYY

 

Result I'm getting with Left-Outer Merge

Item NumberDateID
A1232022-01-01ZZZ
B2342022-01-02 
C3452022-01-02XXX
C3452022-01-02YYY

 

My goal result: 

Item NumberDateID
A1232022-01-01ZZZ
B2342022-01-02 
C3452022-01-02XXX
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @kevinsa ,

 

I suggest you to add an [Index] column to sort your ID by [Item Number] and [Date] group in Right Table.

RicoZhou_0-1675134293659.png

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.

RicoZhou_1-1675134379701.png

 

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.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-rzhou-msft
Community Support
Community Support

Hi @kevinsa ,

 

I suggest you to add an [Index] column to sort your ID by [Item Number] and [Date] group in Right Table.

RicoZhou_0-1675134293659.png

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.

RicoZhou_1-1675134379701.png

 

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.

miTutorials
Super User
Super User

Select the Item Number column and remove duplicates after merging them using full outer join.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.