Way to query data using SQL and csv file

[NancySharma]

I want to query my sql database based on some fields in csv file. 

When i connect to query can i use the query to connect this way for example.

SELECT Column1

      ,Column2

      ,Column3

FROM myDB1.TableName OU

WHERE OU.Column1 IN (

SELECT id FROM  Source = Csv.Document

WHERE CSVColumnName IN (“product”, “abc”));

[Phil_Seamark]

Hi @NancySharma

I wrote a blog explaining one technique you can use in Power Query to generate dynamic SQL

http://radacad.com/dynamic-sql-using-power-query

[NancySharma]

Hi Phil,

I am new to Power BI,

What i see is that you made changes to data on Power BI. What i want is that i get prefiltered data from sql based on filter in CSV file so that only needed data gets loaded in power BI.

Reason is when i try to form joins and filter data in power BI, it takes a lot of time when i refresh my CSV file, it has like million records. So what i am looking for is  i get prefiltered data uploaded to BI based on CSV filer that i can put in query.

[Phil_Seamark]

Hi @NancySharma

All the work should be done in Power Query (which sits before Power BI).  You can query your CSV file and you will have to read all of it.  Then in Power Query, you can generate a list values from the CSV file that you are interested in and finally use that list inside your T-SQL query statement in the WHERE clause.

[NancySharma]

Yes  i tried this approach but apparently when i refresh my csv file this query stops working. Is there a workaround for that or any other solution.

[Phil_Seamark]

Hi @NancySharma

It shouldn't stop.  Power Query should be smart enough to know that it needs to fully load the CSV table to generate the list you need for your SQL data source.  If you click the view dependances button on the PQ ribbon you should see a diagram showing the flow of the logic. 

I think something else might be causing an issue. 

[NancySharma]

It does not work when i move Power BI File  from one user to another. am i doing something wrong?

[Phil_Seamark]

Hi @NancySharma

I'm not sure what you mean by "moving from one user to another"

[NancySharma]

so once i ma able to join the databases and able to design the report the way i want. i save it as Microsoft Power BI Desktop Template (.pbit) and send the same to other users. But wen they open is template and try to run the report by refreshing it does not work.

[Anonymous]

@NancySharma,

What error message do your users get?  Could you please also post the codes in your Advanced Editor? Do mask sensitive data before uploading codes.

Regards,
Lydia

[NancySharma]

It does not work when i move Power BI File  from one user to another

[NancySharma]

Yes  i tried this approach but apparently when i refresh my csv file this query stops working. Is there a workaround for that or any other solution.