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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jesly_ajin
Helper III
Helper III

Dynamic from date and to date in web source query

Hi All,

 

In the below code, I need the bold ones to read data based on user selection of start and end date.

Kindly guide me to achieve this.

 

let
Source = Json.Document(Web.Contents("https://abc.rxyz.io/api/sync/ems/powerbi?date_start=2022-07-01&date_end=2022-07-18", [Headers=[Token="7901d444-a86f-4031-8922-7e0222ae"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"DATE", "COD", "NAME", "PLATE", "TYPE", "ENGINE_ON", "ENGINE_IDLE", "ENGINE_OFF", "CROSSOVER", "BREAKDOWN", "PROJECT_CODE", "PROJECT_NAME"}, {"data.DATE", "data.COD", "data.NAME", "data.PLATE", "data.TYPE", "data.ENGINE_ON", "data.ENGINE_IDLE", "data.ENGINE_OFF", "data.CROSSOVER", "data.BREAKDOWN", "data.PROJECT_CODE", "data.PROJECT_NAME"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data1",{{"success", type logical}, {"data.DATE", type date}, {"data.COD", type text}, {"data.NAME", type text}, {"data.PLATE", type text}, {"data.TYPE", type text}, {"data.ENGINE_ON", type number}, {"data.ENGINE_IDLE", type number}, {"data.ENGINE_OFF", type number}, {"data.CROSSOVER", type number}, {"data.BREAKDOWN", Int64.Type}, {"data.PROJECT_CODE", type any}, {"data.PROJECT_NAME", type any}, {"message", type any}})
in
#"Changed Type"

10 REPLIES 10
AnupaJ
Frequent Visitor

I have the same requirement as this
I need to extract the data of a specified web path but with the date range to change to the year to date to the current date, any idea how can I do this on PBI

http://xxxxxxx.com/itemGroupSalesScorecard.aspx?type=&company=can&businessUnit=&eDate=7/6/2023&sDate...

jesly_ajin
Helper III
Helper III

A slicer date drop down so that user can select.. 

I have created 2 tables for start date & end date using Create table option.

Convert those tables in lists and use List query in parameters. That way users can select dates as per their need. 

Parameters gives following and you need to select last one. You will need to create 2 parameters like this - One for Start and one for End.

Any Value   You can enter any value of any data type in the parameter query.
List of Values    You can limit the values to a specific list by entering them in the small grid. You must also select a Default Value and a Current Value below.
Query   Select a list query, which resembles a List structured column separated by commas and enclosed in braces.

I did create the parameters and even list query for a column. but after doing that I am getting error.

Any other suggestion.

 

Please post the error screenshot. Also, if possible you can post pbix file without data (remove any sensitive information like password etc in that).

Vijay_A_Verma
Super User
Super User

Replace Source with following where RangeStart and RangeEnd are in parameters

Source = Json.Document(Web.Contents("https://abc.rxyz.io/api/sync/ems/powerbi?date_start="&RangeStart&"date_end="&RangeEnd, [Headers=[Token="7901d444-a86f-4031-8922-7e0222ae"]]))

@Vijay_A_Verma , I did create a parameter as suggested from your side.

However, when I look to bind the parameter, i see no option for it.

Kindly guide me with steps to use RangeStart & RangeEnd as parameters.

You need not bind just put the dates such as 2022-07-01 in these parameters. Make sure that you select Text as type for parameter value not as date.

I do not wish to hard code the start & end date. I need it to change based on user selection. Hence trying to bind

What will be the source of your start and end dates? 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors