PowerQuery: Merge Queries HORRIBLE performance

Hi ebailly1 , I have not seen any documentation at all. I have brought this up multiple times with the development team, for instance at MBAS this summer and through a premium support ticket. The issue seems to be:

- If using "left join" type logic - the UI essentially doesn't have to compute anything, it just slaps a dummy column on the end. This displays quickly in the UI because it doesn't really need to do anything

- If using "inner" type logic - the UI should (but doesn't) send a query to the database. Instead, for whatever reason, it pulls in both tables and tries to manually do the join in the Power Query engine instead of pushing it down to the database to do it

We've been working around this in two different ways, either manually adding Table.AddJoinColumn steps, or by always doing left joins. If we do left joins, we then sometimes do a "not null" type test to force the results to be the same as an inner join. My colleague has done some testing, and it seems like it at least some of the cases this actually DOES result in an inner join being sent to the database. We haven't exhaustively tested this though, and not sure if this happens all the time, if steps have to be in a certain order to make it work, etc.

One other note is that once you click on the "Expand" for the column after the merge step - then the UI does start sending the queries to the database again, and performance gets much better. It's ONLY on the merge step that the UI tries to do it itself vs. using the database.

I sure wish Microsoft would fix these issues when hitting SQL server databases:

1. This issue

2. The table/view navigation screen not being able to show synonyms. I can select tables or views, synonyms should show up also and work exactly the same

3. The inability to use a SQL override as the source step, but then being able to add additional steps that still get pushed down (for example, filtering, aggregation, etc.). If it would just submit the query, substituting the SQL in "as if" it were a table name, everything would work fine - and would remove the huge bottleneck that SQL overrides stop getting pushed down

Sorry I don't have better answers...I've been pounding on these with Microsoft for 6+ months at this point but unfortunately don't see any progress being made.

Thanks,

Scott