Forum Discussion

uberdube's avatar
uberdube
Advocate IV
6 years ago
Solved

How does Merged Queries work in PBI Desktop?

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 ...
Need Help
  • uberdube's avatar
    uberdube
    6 years ago

    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

uberdube's avatar
uberdube
Advocate IV
6 years ago

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's avatar
Mariusz
Community Champion
6 years ago

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's avatar
    uberdube
    Advocate IV
    6 years ago

    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