Power Query - Query Folding Help

[Matt Allington]

I am trying to bring in data from SQL Server into Power Pivot (Excel 2013) using Power Query. I want to use Query folding to make the process more efficient. I understand this should be automatic, however the behaviour suggests it is not working.

I have a list of (say 10) product codes that I want to bring sales into Power Pivot. I have loaded a table containing 10 product codes (create connection only) in Power Query.

I then create a new connection to SQL server. I did not write any SQL, but instead browsed to the table from within Power Query Edit. I then created an inner join between this SQL table and the 10 product codes mentioned above. I would expect a query to be pushed to SQL and the small table returned. But instead I see the Power Query status bar cranking through 20 million + rows.

Am I doing something wrong? If I write this query natively, it refeshes in 20 seconds.

 

[ImkeF]

Hi Matt,
do the items in the filter belong to the first 200 items in your SQL-table?
Then query-folding wouldn't happen, as the algorithm then assumes that the filterlist will continue that way and it would be more efficient to start retrieving all the data immediately (this logic in the algorithm should speed up the process, but can actually mislead during development unfortunately)

So if you adjust your filter items to items more distributed around the whole content, it should work.

 

[Matt Allington]

Thanks Imke

This stuff only sticks when I do it, hence why I am trying now. I have 140,000 products in my sales table, and I am trying to extract sales for just 10 of them. can you think of a way I could get query folding to work in this scenario (assuming the 10 products are not the first 10 codes in my product list)?

 

[ImkeF]

Just to reassure: You're connecting to a relational SQL-DB and not to a SSAS-cube?

 

[ImkeF]

How about sharing your workbook? Just change the source-connection to sth meaningless before sending, this would probably be quickest for us both.

 

[Matt Allington]

Just to reassure: You're connecting to a relational SQL-DB and not to a SSAS-cube?

Yes, SQL RDB

 

[anvg]

Hi
Maybe it is better to use dynamic sql query for sql server

let
    //get needed product id from a book table
    neededProduct = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][ProductId],
    toText = List.Transform(neededProduct, each Text.From(_)),
    toQueryPart = Text.Combine(toText,","),
    baseQueryPart = "Select * From dbo.Checks Where ProductId In (",
    source = Sql.Database("(localdb)\mssqllocaldb",
    "SqlServerStudy", 
    [Query= baseQueryPart & toQueryPart & ")"
    ])
in
    source

Regards,

 

[Matt Allington]

Thanks for your offer of help Imke. I did it again in blank workbook and got a very different experience - it seemed to work. There may be something confusing things in my old workbook. I have enough to give me some confidence to move on from here.

Thanks again.

Matt

 

[Matt Allington]

Thanks Anvg

Interesting approach. I basically do this manually already (just manually add the in statement into my queries, not as elegant as your code. I am wanting to use this business problem to learn about query folding specifically, but I will scrape you code for reuse later when I want to write something similar.

 

[ImkeF]

That's great - gook luck then!

Just don't forget the query-bug-wall if your non-SQL-filter-table is longer than 200 rows: Filter SQL-server queries with Excel-tables: Query folding limitations and bug workaround – The BIccountant

That one hurts as well