use parameters in sql query to import data

[santiagomur]

hi, 

i`m trying to use parameters to import data, what i need is to use parameters to change de WHERE in de query. 

I am creating a theme and i want that when i open it they ask me this parameters, the same use as with the database and server.

i know i can use parameters on a filter but i don't want to import all the database

example:
select

* from table1
where date="parameter1" and reportname="parameter2"

[Anonymous]

Hi @santiagomur,

I know you can do this with dates as this is a problem I struggled with for a while when I was implementing incremental refresh for one of my reports.

The way I got it working for Import Mode was:

• In Power BI, set up 2 Date/Time parameters. One for your start date and another for your end date (It sounds like you might have already done this)
• In your SQL query, DECLARE 2 variables/paramteters as varchar(19) and another 2 variables as date. E.g.

DECLARE 
@RangeStart as varchar(19), 
@RangeEnd as varchar(19), 
@ReportStart as date,
@ReportEnd as date

• In SQL, set your RangeStart and RangeEnd variables with arbitrary long date time values, and set the ReportStart and ReportEnd variables as a conversion of these to datetime. Use ReportStart and ReportEnd in your WHERE clause:

SET @RangeStart = '2020-01-01 00:00:00'
SET @RangeEnd = '2020-01-02 00:00:00'
SET @ReportStart = CAST(@RangeStart as datetime)
SET @ReportEnd = CAST(@RangeStart as datetime)

• Once this query is in Power BI, click Home -> Transform Data, then click "Source" under the "Applied Steps" pane
• In the query bar, find the part of your query that SETS your @RangeStart and @RangeEnd parameters. Replace the date with

SET @RangeStart = '"&DateTime.ToText(Parameter1,"yyyy-MM-dd hh:mm:ss")&"'
SET @RangeEnd = '"&DateTime.ToText(Parameter2,"yyyy-MM-dd hh:mm:ss")&"'

When you refresh your data, your Power BI paramteter values should now be passed to the SQL query.

Cheers,

Andy

[Anonymous]

@Anonymous just out of curiosity, how did you solve the incremental refresh? Because I tried to have one big table with "old" data and one smaller for recent data, then instructed PBI to load only the small one and then concatenate them. No matter how hard I tried, PBI always refresh everything - I suppose it's a way MS implemented to avoid people working with incremental refresh without buying the Premium...

[JirkaZ · Accepted solution]

@Anonymous And here you go - Feb update with Incremental Refresh for PRO users 🙂

[Anonymous]

whooooooooooooooaaaaaaaaaaaaaaaaaaaa!!!!!!!!! Did they read our mind? 🙂

[Anonymous]

Hi @Anonymous,

I've been primarily working with premium workspaces in and "App Owns Data" embedded environment, so I've used the out of the box methods, tied in with the 'jiggery-pokery' I posted earlier. I've not even considered how a similar solution might be obtained in a non-premium environment. 

Your solution sounded interesting on the face of it, until you consider a scenario where data from your "new" table reaches the age threshold whereby it is no longer considered new and therefore, presumably, moved into the "old" table. In this case, you not only have to refresh your "new" table, but also your full "old" table which is likely to be much larger. 

Depending on your use case, running a full refresh isn't necessarily a bad thing. You simply have to consider the tradeoff between cost, speed, and data availability (Ask the question of your users: Do you really need to see an entire history , or do you mostly look at the last X days/weeks/months?).

[Anonymous]

@Anonymous 

All your considerations are correct and things I faced. In my test case (last year) I was downloading 2010->2018 data in a big one-off file and 2019 in the update table. Then the idea was to change the model in 2020. 

The idea was to speed up import because loading 4M rows where 3.9M haven't changed is much inefficient than loading just the 100k that have been updated. Unfortunately it does not work. Honestly I do not remember why but I did a few tests and PowerBI was refusing to work. I don't remember if it was updating the data EVEN IF was selected not to refresh that table or what, but I spend a lot of time trying to make it work with no success (and I thought about it: it was a nice way to go around the MS limitation of incremental refresh so maybe they thought about it 🙂 )

If I remember correctly it was like with Excel files: when you load an Excel file on a PBI and then publish to web, NO MATTER HOW YOU DISABLE REFRESH it will still forever try to load that local file on the web, failing. The only way to load "one off" an excel file is to push the data as a loaded table in PBI.

[JirkaZ]

@Anonymous Just disabling the "Include in Report Refresh" should cause the engine to not refresh the larger table.
However this "manual" incremental refresh is kinda hard to achieve since from time to time you have to "flush" the data to the "archive" data source and hence update it all. 

[amitchandak]

Check if these can help:

https://community.powerbi.com/t5/Desktop/Using-Advanced-Query-and-parameters-to-create-dynamic-SQL/m...

https://community.powerbi.com/t5/Desktop/SQL-parameters-in-direct-query/m-p/248180

https://community.powerbi.com/t5/Power-Query/parameters-to-SP-or-Sql-query/m-p/57734

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[JirkaZ]

@santiagomur Power BI uses a model-first approach so unless you go to the query editor, you cannot change the scope of the loaded data. Consider connecting to the DB using Direct Query then.

[Anonymous]

Well, you can set the parameter and use it in your query. For example I use a paramenter to switch all my datasources from test/production db:

Origin = MySQL.Database(server_port, "datawarehouse", [ReturnSingleDatabase=true]),

and server_port is a parameter that I change once.

However I'm not aware of any method to force the db to refresh when you change a parameter. You can change the parameter and then force - manually - a refresh data.