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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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