Power Query Merge - take only first match

[kevinsa]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-rzhou-msft · Accepted 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.

[miTutorials]

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