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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
MarcaSolkanar
Regular Visitor

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 3
v-kkf-msft
Community Support
Community Support

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

vkkfmsft_0-1654149279443.png

vkkfmsft_1-1654149289529.png

 

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.

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.

MarcaSolkanar_0-1654536392220.png

I created a Custom Column in Power Query

 

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

MarcaSolkanar_1-1654536486834.png

I wait for the message to end.

MarcaSolkanar
Regular Visitor

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.