cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Fuzzy Merge Advanced Power Query

Hello,

I am trying to acheive this function;

- I have table of Budget line connected to a Unique product ID and to invoice associated

Buget_Request_numberBudget_Item_NumberInvoice-VoucherProduct_NumberUnique Budget line
BR907886531890987845WR7890Budget_Request_number&Budget_Item_Number&Product_Number



- I have a Table of payment linked to the Unique product ID. 

Description Invoice-VoucherProduct_Number(Does not exist yet in this table) PO_line_number
Payment 890987845WR7890Buget_Request_number&Budget_Item_Number&Product_Number


What I am trying to acheive:

I am trying to add the column PO_line_number (in red) into the second table. 

Problems:
In the second table, the column Description Invoice-Voucher has the invoice-voucher number in the description. It not a 100% matching Field.
The Product numbers are very close to each other (one character different)


What I have tried: 

- After isolation the maximum the invoice voucher from the description field
- I am using a concatenation: Product_number&Invoice_Voucher.
- I have tried to use the fuzzy merge function, the problematic is related to the product number. The system get the right invoice with the wrong product. 


Table.FuzzyNestedJoin(table1 as table, key1 as any, table2 as table, key2 as any, newColumnName as text, optional joinKind as nullable number, optional joinOptions as nullable record) as table


What I would try: 

For each row from the second table,
1) read the product number
2) filter the first table
3) Fuzzy merge the filter table

 

 

Any idea on how to solve this? 

 

 

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Fuzzy Merge Advanced Power Query

Hi @alexandre_74890 ,

Maybe you can consider to extract similar parts as new column to use fuzzy merge function.
In addition, can you please share some sample to help us clarify your query table steps?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 39 members 1,086 guests