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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
arkiboys
Advocate II
Advocate II

conditional join

hello

in fabric dataflow, I am joining two excel files via several columns.

file1.col1 inner join file2.col1

file1.col2 inner join file2.col2

how is it possible to use file1.col join file2.col only if file1.col4 has a value?

thanks

1 ACCEPTED SOLUTION
v-cboorla-msft
Community Support
Community Support

Hi @arkiboys 

 

Thanks for using Microsoft Fabric Community

Here's how to join two Excel files in Fabric Dataflow with an inner join based on multiple columns, considering a value in a separate column. Fabric Dataflow offers a powerful tool called Filter that allows you to achieve this conditional join.

vcboorlamsft_2-1717152160346.png

I hope this information helps. Please do let us know if you have any further queries.

 

Thank you.

View solution in original post

9 REPLIES 9
frithjof_v
Resident Rockstar
Resident Rockstar

Could you create one (or more) custom column (populated with values only as per your conditions) and then use the custom column in the join instead of the original column(s)? 

 

Or maybe you can edit the power query code in Advanced Editor to facilitate your needs.

 

(Otherwise, I would consider posting this in the Power BI desktop forum because this is essentially a universal Power Query question, not specific to dataflows gen2, and there may be some Power Query experts in that forum)

Hi @arkiboys 

 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

 

Thank you.

Hi @arkiboys 

 

Glad that you query got resolved.
Please continue using Fabric Community for further queries.

 

Thanks.

v-cboorla-msft
Community Support
Community Support

Hi @arkiboys 

 

Thanks for using Microsoft Fabric Community

Here's how to join two Excel files in Fabric Dataflow with an inner join based on multiple columns, considering a value in a separate column. Fabric Dataflow offers a powerful tool called Filter that allows you to achieve this conditional join.

vcboorlamsft_2-1717152160346.png

I hope this information helps. Please do let us know if you have any further queries.

 

Thank you.

thank you for your reply however I am looking for a way to do condition on inner join when merging the two datasets.

Hi @arkiboys 

 

After using the filter option, you can see the File1 that contains the columns which have only values.

vcboorlamsft_2-1717152589793.png

You can see the result as below as there is only one common column.

vcboorlamsft_1-1717152509446.png

 

I hope this information helps.

 

Thank you.

thanks for your message however your test is not as per my requirement in the original thread.

Hi @arkiboys 

 

Apologies for the inconvenience from my end.

Could you please elaborate on the issue you are encountering by sharing additional details, such as screenshots, would be very helpful in understanding the problem and guide you in better way.

 

Thank you.

hi,

here is the scenario:

1- two sources

2- each source has columns as follows:

3- file1.col1, file1.col2, file1.col3, file1.col4

4-file2.col1, file2.col2, file2.col3, file2.col4

5- I would like to do an innerjoin using the merg functionality in dataflow on multiple columns as follows

6- the sql format of the join is like this but want to know how to do this in dataflow using merge

file1.col1 = file2.col1

and

file1.col2 = file2.col2

and

--use these columns in join if file2.col3 is not null and file2.col3 > 0

(

file1.col3 = file2.col3 --only if file2.col3 is not null

and file1.col4=file2.col4 --only if file2.col4 > 0

)

Helpful resources

Announcements
Sept Fabric Carousel

Fabric Monthly Update - September 2024

Check out the September 2024 Fabric update to learn about new features.

Expanding the Data Factory Forums

New forum boards available in Data Factory

Ask questions in Apache Airflow Job and Mirroring.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors