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 only if file1.col4 has a value?

thanks

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.

9 Replies

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Microsoft Employee
    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.

  • frithjof_v's avatar
    frithjof_v
    Community Champion
    2 years ago

    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)

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

      Hi Anonymous 

       

      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.

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

        Hi Anonymous 

         

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

         

        Thanks.