Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lbrumer
Regular Visitor

How to pass the date parameters in PQ Query?

Please let me know if the following future is available in Power BI:

Dynamically pass date as parameter to stored procedure.

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

Hi @lbrumer,

 

With the Query Parameters feature, we can define one or multiple parameters to be used in the queries, Data Model and report layers in Power BI Desktop.

 

For example, to pass the date parameters in PQ Query, you can create a new Query Parameter of Date, then use this date parameter to filter your corresponding date column in the Query like below. For more details about POWER BI DESKTOP QUERY PARAMETERS, you can refer to this article. Smiley Happy

 

dateparameter.PNG

let
    Source = Sql.Databases(""),
    AdventureWorksDW2012 = Source{[Name="AdventureWorksDW2012"]}[Data],
    dbo_DimDate = AdventureWorksDW2012{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey] > DateParameter)
in
    #"Filtered Rows"

 

Regards

View solution in original post

3 REPLIES 3
v-ljerr-msft
Employee
Employee

Hi @lbrumer,

 

With the Query Parameters feature, we can define one or multiple parameters to be used in the queries, Data Model and report layers in Power BI Desktop.

 

For example, to pass the date parameters in PQ Query, you can create a new Query Parameter of Date, then use this date parameter to filter your corresponding date column in the Query like below. For more details about POWER BI DESKTOP QUERY PARAMETERS, you can refer to this article. Smiley Happy

 

dateparameter.PNG

let
    Source = Sql.Databases(""),
    AdventureWorksDW2012 = Source{[Name="AdventureWorksDW2012"]}[Data],
    dbo_DimDate = AdventureWorksDW2012{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey] > DateParameter)
in
    #"Filtered Rows"

 

Regards

Hi,

I am getting data from an API that has two parameters like below in the URL:

 

Param1930=2021-01-01&Param1931=2030-12-31&format=JSON

 

These are the start and end dates of the report that I have manully set up in the API URL. But, how do I make it dynamic so that eveyday that I refresh the data in Power BI connect to the API and pull the latest data? My current M code is something like below:

 

let
Source = Json.Document(File.Contents("C:\Users\Ali\AppData\Local\Microsoft\Windows\INetCache\IE\EHD7MHFN\1120")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"txtProvider",
"txtIncidentNo",
"FltDate",
"LogTime")

 

I have tried to apply your solution but when I replaced Param1930 and Param1931 in the custime query, I get an error saying that there is no such param in the source which is correct as you could see in above. Those params are initially in the API URL that I get the data but now the source has been changed to my own directory.  

Hello,

 

Is there a way to give the user the ability to choose the date instead of hard coding it?

 

Regards.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors