Parametrize SQL Query in Power Query

[Anonymous]

Hi all,

My data source is Hive server, and I used SQL query to load data into power query my concern is that data table has millions of rows in table.

Can we parametrize SQL query to load only 1 days of data for development purpose, I want to load complete data on power bi service by changing the parameter on service.

I have created parameter but didn’t work for me.

My Sql query is like that :-

Source = Odbc.Query("dsn=Hive199", "select concat(weeknumber, daynumber) as WeekDay, ColumnA , viewingdate, Column B  from hive.level1.Table1 ;")

Want to run paramter on viewingdate column.

[v-zhangti]

Hi, @Anonymous 

Maybe you can try to configure incremental refresh. Configuring incremental refresh includes creating RangeStart and RangeEnd parameters, applying filters, and defining an incremental refresh policy. After publishing to the service, you perform an initial refresh operation on the dataset. 

The RangeStart and RangeEnd parameters allow you to filter the data to be loaded into the model based on these parameters.

You can refer to the following document, hope it can help you.

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SergioSilvaPT]

Hi @Anonymous ,

To use a parameter you must do it this way:

  Source = Odbc.Query("dsn=Hive199", "select concat(weeknumber, daynumber) as WeekDay, ColumnA , " & viewingdate & ", Column B  from hive.level1.Table1 ;")

Surrounding the parameter with " & parameter & "

Regards,

Sérgio Silva 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

[Anonymous]

@SergioSilvaPT Thanks for the reply but that is not my parameter, Its a column viewing date
I want to apply parameter on this viewing date like only load 1 day of data in power bi desktop for development and complete data on power bi service.

[SergioSilvaPT]

@Anonymous ,

You referenced that you wanted to change the parameter in service, and for that you need to create the parameter in Power BI Desktop and use it like i mentioned.

Probably i didn't undestand your problem...