Power Query - Web connector with variables

[Raggsokk]

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?

[ChielFaber · Accepted solution]

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)

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.

[Raggsokk]

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