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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Raggsokk
Frequent Visitor

Power Query - Web connector with variables

I'm struggling to understand how I can use variables in a power bi query.

I would like to get the last 6 months of data from a api connection.

 

I can get the data and it looks like this.

 

= Json.Document(Web.Contents("https://api.sendgrid.com/v3" & "/stats?start_date=2023-08-01", [Headers=[Authorization="Bearer xxx"]]))

 

Is there a way the date field could be dynamic?

1 ACCEPTED SOLUTION
ChielFaber
Super User
Super User

I've tackled this "problem" a few times with the solution provided by Patrick LeBlanc in the following youtube video: https://www.youtube.com/watch?v=Z0U9UL9ORh8 .

 

You need use a parameter and make the first script into a function. The first parameter inout for the trial script should be the text you need to pull in the first data: /stats?start_date=2023-08-01

 

When you've created the function you need a table to which you can add a column. Whereby there's a column value that you can use as a parameter. To make it dynamic you need to create a table with the dates that you need. You can do this manually but you can also make it dynamic by building a date table and filter that table with only the first day of every month. This table will grow with your report/solution.

 

For an example how to create a date table in PowerQuery you could check out this usefull blogpost: https://gorilla.bi/power-query/date-table/ . 

 

When you have the date you need. You could then use this date to create the parameter input. Change the date column to text and create a new column. In this new column you can create the input:

 

"/stats?start_date=" & [Date_column]

 

Let's name this column input. You can then add a new function column. (Example in dutch)

Add function column.PNG

 

It will ask for an input column.Just chose the just created column we called input. This will add a table for every row. The last step would be to click expand. This should get you a table with all your data.

View solution in original post

2 REPLIES 2
ChielFaber
Super User
Super User

I've tackled this "problem" a few times with the solution provided by Patrick LeBlanc in the following youtube video: https://www.youtube.com/watch?v=Z0U9UL9ORh8 .

 

You need use a parameter and make the first script into a function. The first parameter inout for the trial script should be the text you need to pull in the first data: /stats?start_date=2023-08-01

 

When you've created the function you need a table to which you can add a column. Whereby there's a column value that you can use as a parameter. To make it dynamic you need to create a table with the dates that you need. You can do this manually but you can also make it dynamic by building a date table and filter that table with only the first day of every month. This table will grow with your report/solution.

 

For an example how to create a date table in PowerQuery you could check out this usefull blogpost: https://gorilla.bi/power-query/date-table/ . 

 

When you have the date you need. You could then use this date to create the parameter input. Change the date column to text and create a new column. In this new column you can create the input:

 

"/stats?start_date=" & [Date_column]

 

Let's name this column input. You can then add a new function column. (Example in dutch)

Add function column.PNG

 

It will ask for an input column.Just chose the just created column we called input. This will add a table for every row. The last step would be to click expand. This should get you a table with all your data.

That youtube video was perfect. Just what I needed, thank you.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors