Pulling in variable to Power Query

[mattadams84]

I am trying to pull in a variable from an excel sheet to use in power query.

I am basically calling an API and use the following code (this works perfectly by the way)

let
    pagination = (Page as number)=>
let
    Source = Json.Document(Web.Contents("https://api.my-data-api.com/todays-data?key=xxxxxxxx&date=" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), 10), "YYYY-MM-DD")&"&page="&Number.ToText(Page))),
    data = Source[data]
in
    data
in
    pagination

What i want to be able to do is change the numerical value of

Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), 10),

So in this case i want to change the number 10 and to get that number from a sheet in my workbook.

I have created a table on a worksheet and called the table GET_DATA. This is a one row table with the number contained in a column called "ID"

How can i change this number "10" currently hardcoded to be a variable?

 

[Shortmeister1]

Just copy in the steps from the parameter query after LET and make sure that they have unique step names. You can then refer to the step name further down.

(BTW, you don't need to use double quotes in step names or # unless there's a space in the step name. No idea why Microsoft did this by default. It's a bit of a workaround to a problem they created)

 

[mattadams84]

OK, great, i copied that across and it didnt throw up the firewall error. Now it just asked me to confirm the privacy settings, to which i said ignore privacy data sharing, and it works. I can see why they have these privacy settings, but in my case its only me who will use the file, and im merging data from a table i created to data pulled from an API so there is not much risk! Many thanks for your help!

 

[Shortmeister1]

A pleasure.