Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 on...
Dataflows
  • v-cboorla-msft's avatar
    v-cboorla-msft
    2 years ago

    Hi Anonymous 

     

    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.

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

     

    Thank you.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

v-cboorla-msft's avatar
v-cboorla-msft
Microsoft Employee
2 years ago

Hi Anonymous 

 

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

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

 

I hope this information helps.

 

Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    • v-cboorla-msft's avatar
      v-cboorla-msft
      Microsoft Employee
      2 years ago

      Hi Anonymous 

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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

        )