Forum Discussion
Multiple active table relation: IF exist 1st relation THEN use it, ELSE use other
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 💕
3 Replies
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 SelectRows1If 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.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.
Do you know if it's not possible of it is not clear the question?
