Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@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
@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