How does Merged Queries work in PBI Desktop?

[uberdube]

Hi,

Apologies if this is a noobie question.. but I just can't seem to figure this out. Hopefully someone will be able to just give me a 'general' explanation if one exists, otherwise I will post a specific example...

Table A has 20 values - it is a pre-filtered list of unique ID's that I want to use as the 'base' for a merged query with Table B (ie. i only want to join rows from Table B that contains the ID's in Table A).

Table B has ~7 million rows in total.

If I perform a inner join On Table A & B using a SQL query, I result in around 200,000 rows being returned.

However - if I use Power Query 'Merge Queries' function in Power BI Desktop (ie. merging Table B onto Table A), it proceeds to import/evaluate the entire 7 million rows, meaning I have to wait horribly long periods of time just for this process step to finish/evaluate before seeing any results or being able to move on.

I was under the impression that 'Merge Queries' was the equivalent of a SQL join, but this scenario doesn't seem to be processing in the same manner when it seems you have to import your entire source table first, to then perform the join..?

Any help to understand the logic behind what's happening here in Power Query would be much appreciated.

[Mariusz]

Hi @uberdube 

Have you selected the right type of Join ( see the example below )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[uberdube]

Hi @Mariusz 

Thanks for your reply - I'm pretty sure my join looks fine:

What I have noticed though is that my Native query seems to be greyed out once arriving at the Merge step.. I guess that means that query folding isn't occurring and maybe why thats why the data is being pushed into the model, to enable the join query to be processed there instead of on the SQL Server? Is that the 'normal' behaviour for merge tables in Power Query (ie. merging/joins are not classed as 'native' processing in M query?)

Thanks,

Greg

[Mariusz]

Hi @uberdube 

Table.NestedJoin is supported by QF, so my guess is that the "zStaging_CAP" is braking it because of some reason.
1. Are both tables from the same SQL Server DB?
2. Can you see Native Query in "zStaging_CAP"?

Many Thanks

Mariusz
 

[uberdube · Accepted solution]

Hi @Mariusz apologies for delayed response, have not been able to get back to this until now.

You are correct - I have a step in the M Query to add a row to zStaging_CAP using the 'Table.InsertRows' function, this is where the native query folding failed. It looked something like this:

let
Source = LinkedTable,
#"Add Row" = Table.InsertRows(Source,0,({<Field Values>}))
in
#"Add Row"

I'm not sure why this function made the query folding fail, but this was definately the reason and when I remove it the query folding is now fine. I will try to find another method to solve the problem (that doesn't break the query folding) and see if this has any effect on my original issue of it importing all the million of rows before doing the Inner join.

Thanks very much for your help.

Cheers,

Greg