Avoid large data to retrieve for the need of small data in Power query.

[mahenkj21]

Hi,

I would like to find out what is the way to retrieve a small set of data from SQL server wherein the millions of data record are available.

I am working in a power bi solution in that I am connecting to SQL server for my data SQL server has millions of data and I am writing the SQL query in which may be two or three tables are join together and then I retrieve the data of my interest. I am using where condition where I can filter certain products but this is a kind of hardwire solution, I want to keep it flexible. To achieve this, I want to make a "list" in which products of interest are in that list and I want to run SQL query based on that list.

I do understand there is a solution wherein I can refer the said list in "Advanced editor", filtering the result for products in the "list" and that works well. The problem is when the query runs, it first retrieve millions of data because at first filter is not used and then my list is used to filter the data so final solution is what I need but it takes longer time in processing.

I am interested to understand is there any solution in which my list can be embedded in the SQL query which then will connect to SQL source and my list will act as the live filter while running the query so it at first only retrieve a very limited number of data.

Thank you.

[parry2k · Accepted solution]

@mahenkj21 I'm not suggesting to do  where condition manually in sql query, value in where condition will come from excel sheet where you have list of required product.

Attached excel sheet will help you to get it going. So in example, I have two parameter in filter sheet, when string and other number. I connected to my sql serve and based on teh values in agentkey and agentname I want my query to return the result.

So I connect to my filter sheet, create two example for string and integer coz there is different step in transformation. and then i passed it to my sql query whcih return only rows i'm interested in.

If you need further help, let me know but it is pretty self explanatory, you can look at the steps in power query.

PS - You will not able to refresh the data because it is connect to sql server local on my computer.

[parry2k]

@mahenkj21 you can use sql query in power query and dynamically create list in where condition, in that case you will get only filtered data instead of millions of rows.

Question, how you are plannng to maintain that list or how you will change the values in the lists?

[mahenkj21]

Hi,

Thank you, sorry for delay in reverting back.

Regarding your question, I would create a table and then convert it into a "list" in Power query. I would keep updating the table when I need to add new items.

Can you please propose a solution in this regard or even something more better way!

Just to be clear, I have used each list.contains solution (found in another question) at this forum. So, in original sql query, I don't use where condition and then in the advanced editor I add a filter as each list.contains. This first pulls all data as written in sql query (millions of rows) and then filter that to the list (few hundreds). The final solution is fine to me but it takes a longer time to process.

[parry2k]

@mahenkj21 since you are planning to maintain a table (i guess in sql) then it is easier to use sq query instead of using power query list function. It is much simpler.

[mahenkj21]

I have created an MS Excel workbook power query qry1, in which I fetch data from sql server which gives me many product items, I need only specific ones. I maintain another power query qry2 as a list of products of my interest in the same workbook. Then I refer this list qry2 in qry1 as filter.

When qry1 is processed, it first fetches all data from sql server as I don't use where condition, then refers to the list and loaded data is as trimmed. When processing happens, I can see in the query pane that many million rows are being processed and then once qry2 is referenced, data limits to hundreds for qry1.

Reason for not using where condition in sql query qry1 is I do not want to update the sql statement manually, again and again, instead I just want to update another table of products once a while.

[parry2k · Accepted solution]

@mahenkj21 I'm not suggesting to do  where condition manually in sql query, value in where condition will come from excel sheet where you have list of required product.

Attached excel sheet will help you to get it going. So in example, I have two parameter in filter sheet, when string and other number. I connected to my sql serve and based on teh values in agentkey and agentname I want my query to return the result.

So I connect to my filter sheet, create two example for string and integer coz there is different step in transformation. and then i passed it to my sql query whcih return only rows i'm interested in.

If you need further help, let me know but it is pretty self explanatory, you can look at the steps in power query.

PS - You will not able to refresh the data because it is connect to sql server local on my computer.

[mahenkj21]

Hi,

Steps are pretty clear to replicate.

But there is an error when I implement it, please suggest what to do in this case:

Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

best regards.

[parry2k · Accepted solution]

@mahenkj21 choose ignore privacy levels