Forum Discussion
Conditional Join in PowerQuery Advanced editor?
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.
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.
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.
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.
Perhaps if you shared some data and told us what your end goal was vs saying "this is how I do something in SQL, but Power Query won't do it" we could help. Power BI and SQL Server have a lot of the same logic, but self-joins in Power BI can be very inefficent. There may be other ways.
But starting off with trying to make Product A behave the same as Product B can often take us down the wrong path.How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
