Forum Discussion

McSarah's avatar
McSarah
Icon for Helper I rankHelper I
6 years ago

Conditional Join in PowerQuery Advanced editor?

I am hoping to replicate the following SQL self join logic in Power Query (or DAX I guess) -- NOT as several sequential steps (join on A, create conditional column for B, filter on B conditional colu...
Need Help
Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

Hi McSarah ,

 

If you connect to SQL Server, how about typing your sql sode here?

Reference: Connect Power BI to SQL Server

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • McSarah's avatar
    McSarah
    Icon for Helper I rankHelper I
    6 years ago

    Unfortunately, I am not connecting to SQl server in this case. These data come from flat files. If I were connecting to SQL, I would do this join there.

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      McSarah then you cannot do this. Power Query must read all data in the flat files to know what to pull. There is no server available to handle that filtering for you.

       

      Also, you should never use that advanced SQL box. It has a number of bad side effects:

      • Can cause refresh issues in the service as your permissions have to be elevated.
      • It breaks all further query folding in Power Query.
      • It prevents Incremental Refresh from working at all.

      Either do the full query in Power Query, or create a view on the SQL Server. Both will avoid all of the issues listed above.

       

      Please mark one of these posts as the solution so this thread can be marked as solved to assist others that may be searching for similar info.

      • McSarah's avatar
        McSarah
        Icon for Helper I rankHelper I
        6 years ago

        I am not trying to filter on the initial data pull from the files, I'm trying to filter inside a join downstream from the import -- I am comparing/ self joining tables after I've already imported and merged the source files into a single tall table. Power BI can handle the initial imports just fine; what I'm trying to avoid is getting an extremely large intermediate product on the downstream self join in Power Query.