Multiple active table relation: IF exist 1st relation THEN use it, ELSE use other

[MarcaSolkanar]

Dears ❤️

I would like to create a conditional relation between two tables.

To be more explicative as possible, I have a table (Database1) with Purchase Request created in a site [Division], and another table (Database2) with contract related to materials.

This contract table sometimes is applicable to a specifica site, but sometimes is a generic contract, applicable to every site.

With VLOOKUP, for [Document], in excel I would do:

IF.ERROR(

IF.ERROR(

VLOOKUP(CONCAT([Material];[Division]);$C$1:$E$5;2;FALSE);

VLOOKUP([Material];$C$1:$E$5;2;FALSE));

"null")

Here 2 Database example, with the expected Union result.

Database1 - Purchase req

PR	Material	Value	Division
1000371234	DTR0010159465	135,00	7036
1000471458	DTR0019192615	1500,00	7036
1000519157	DTR0010385169	380,00	7036
1000533978	DTR0010406888	4.160,00	7036

Database2: Contracts

Material	Division	MatDiv	Document	Qty
DTR0010159465	7036	DTR00101594657036	4800000607	50,000
DTR0019192615		DTR0019192615	4800000814	1800,000
DTR0019610249	7004	DTR00196102497004	4800008023	300,000
DTR0010297308	7036	DTR00102973087036	4600080016	2,000

Union:

PR	Material	Value	Division	Document	Qty
1000371234	DTR0010159465	135,00	7036	4800000707	50,000
1000471458	DTR0019192615	1500,00	7036	4800000414	1800,000
1000519157	DTR0010385169	380,00	7036
1000533978	DTR0010406888	4.160,00	7036

Thank you in advance for you help!

Cheers 💕

[v-kkf-msft]

Hi @MarcaSolkanar ,

Please add the custom column in PQ.

Tab = 
let 
  a = [Material], 
  b = [Division],
  SelectRows1 = Table.SelectRows(Contracts, each [Material] = a and [Division] = b),
  SelectRows2 = Table.SelectRows(Contracts, each [Material] = a)
in 
  if Table.IsEmpty(SelectRows1) then SelectRows2 else SelectRows1

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarcaSolkanar]

Hi @v-kkf-msft ,

first of all thank you for your answer.

I'm trying to apply you formula but I don't know why it's taking so long and with this data weight in the window.

I created a Custom Column in Power Query

Consider that the data source is for PR just 2 MB, and contracts 500 kB.

I wait for the message to end.

[MarcaSolkanar]

Do you know if it's not possible of it is not clear the question?