Rebuild this data combination

[newgirl]

Hi!

I had this recent error of "...references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

This error seemed weird for me because I have been refreshing my pbix fine before and suddenyl, I get this. Anyway, sure enough, I understand where it's coming from because I have one query from an SQL data source and I merged it with another query from a SharePoint Excel file.

I followed the steps from https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

and the refresh works now, but my concern is that the refreshing time doubled since the duplicate SQL query is now being refreshed and loaded as well.

Is there some modification I can further do to have the duplicate query not double the refresh time?

The original query and duplcate query 

[vanessafvg]

in my experience Sharepoint takes a lot longer to load than SQL, but I am not sure that answers your question.

[newgirl]

Oh...in my case, I have 400K+ rows from the SQL data source while my SharePoint data sources have a range of 20 to 100 rows only, which is why it takes so long for my SQL data source, around 40 minutes). And because of the duplicate query, it takes more than an hour now 😞

But thanks for sharing!

[vanessafvg]

when you say a duplicate query what do you mean, that it is running 2 queries now?  I assume  they are not actual duplicates of each other?   Have you checked if you are doing query folding?  which is sending the query for the sql server to process rather than your machine?

https://community.powerbi.com/t5/Community-Blog/Query-Folding-in-PowerBI/ba-p/1853138

Are you bringing back any unncessary columns you dont need, have you checked that you are only bringing back the rows you need?  Writing a sql statement to only bring back what you need might help unless you need all of it?   I dont quite understand what your purpose, you are pulling 2 different tables one from sharepoint, another from sql.  What was the purpose of rebuilding in you scenario?     Are you bringing in your tables first and staging them and then only merging  information?

[newgirl]

Hi @vanessafvg ! Maybe I shouldn't have used "duplicated query" to avoid confusion. What started this is that I started getting this error "...references other queries or steps, so it may not directly access a data source. Please rebuild this data combination." during refresh of pbix and after googling, found this article.

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

In it, it explains that for query that is having the error,the source inside should just reference another (new) query wherein that (new) query is where the data source link is stored and then the original query would just reference the source from the new query, to avoid combining an external data source with another query.

So after 'apply and close', I tried to "refresh all", and found out that the new query is also being loaded, which is causing the longer refresh time. Basically, the solution I found worked but the consequence is having longer refresh time due to the new query...unless there is also a solution to have the new query NOT load into the data model but the data would still be refreshed.