cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Nick123 Frequent Visitor
Frequent Visitor

Power Query - Dynamic DateTime Parameters in JSON

Hi,

 

Recently I succeeded in connecting Power Query with an API of a supplier to download data. I can only download 31 days at a time. Can anyone assist me in doing so?

 

In below is the working version for a single period. I was planning to turn the query into a function and make "from" and "to" dates as text parameters, but I ran into a problem "Web.Contents failed to get contents from... (500)" because of introducing the dates as parameters.

 

let
Login = Json.Document(Web.Contents("https://app.com/login?apikey=test", [Headers=[#"Content-Type"="application/x-www-form-urlencoded", Accept="application/json"], Content=Text.ToBinary("username=user&password=password&market=BE")])),
sessionId = Login[sessionId],
body = "{
""from"": ""2018-12-22 00:00:00"",
""to"": ""2019-01-18 00:00:00"",
""events"": [],
""venues"": [],
""admin"": [],
""promoter"": []
}",
Source=Json.Document(Web.Contents("https://app.com/eventSales?apikey=test", [Headers=[#"sessionId"=sessionId, #"marketcode"="BE", Accept="application/json",#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),
in
Source

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Power Query - Dynamic DateTime Parameters in JSON

@Nick123,

Please change your power query code to the following and check if it works.

let
    Source1 = (param1 as date, param2 as date) =>


let 
Login = Json.Document(Web.Contents("https://app.com/login?apikey=test", [Headers=[#"Content-Type"="application/x-www-form-urlencoded", Accept="application/json"], Content=Text.ToBinary("username=user&password=password&market=BE")])),
sessionId = Login[sessionId],
body = "{
""from"": """& Date.ToText(param1) & """,
""to"": """ & Date.ToText(param2)&""",
""events"": [],
""venues"": [],
""admin"": [],
""promoter"": []
}",
Source=Json.Document(Web.Contents("https://app.com/eventSales?apikey=test", [Headers=[#"sessionId"=sessionId, #"marketcode"="BE", Accept="application/json",#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),
in
Source

in
   Source1



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Power Query - Dynamic DateTime Parameters in JSON

@Nick123,

Please change your power query code to the following and check if it works.

let
    Source1 = (param1 as date, param2 as date) =>


let 
Login = Json.Document(Web.Contents("https://app.com/login?apikey=test", [Headers=[#"Content-Type"="application/x-www-form-urlencoded", Accept="application/json"], Content=Text.ToBinary("username=user&password=password&market=BE")])),
sessionId = Login[sessionId],
body = "{
""from"": """& Date.ToText(param1) & """,
""to"": """ & Date.ToText(param2)&""",
""events"": [],
""venues"": [],
""admin"": [],
""promoter"": []
}",
Source=Json.Document(Web.Contents("https://app.com/eventSales?apikey=test", [Headers=[#"sessionId"=sessionId, #"marketcode"="BE", Accept="application/json",#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),
in
Source

in
   Source1



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.