topic Re: API query return a range of selection in Power Query

API query return a range of selection

clubspec — Thu, 17 Jun 2021 02:58:16 GMT

Hi Experts,

I have an API key like below that is working, no problem. However, how do I modify my query to return more than one objectno? For example objectno range that is 001 to 020?

Json.Document(Web.Contents("https://csv.business.tomtom.com/extern?account=AAAc&username=XXX&password=PPP&apikey=818c28e9-09ec-4a3d-b775-2101f4a750be&lang=en&action=showTripReportExtern&rangefrom_string=01/01/2021%2008:00:00&rangeto_string=15/06/2021%2023:59:00&objectno=001&outputformat=json"))

Thanks,

Raymond

Re: API query return a range of selection

mahoneypat — Fri, 18 Jun 2021 01:57:50 GMT

Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let Source = List.Numbers(1,19,1), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), Custom1 = Table.TransformColumns(#"Changed Type", {"Column1", each Text.PadStart(_, 3, "0"), type text}), #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Json.Document(Web.Contents("https://csv.business.tomtom.com/extern?account=AAAc&username=XXX&password=PPP&apikey=818c28e9-09ec-4...objectno=" & [Column1]&"outputformat=json"))) in #"Added Custom"

Pat

Re: API query return a range of selection

clubspec — Fri, 18 Jun 2021 03:53:21 GMT

Thanks Pat but it returns error:

"DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value=,
Position=4"

If I query one objectno at a time, it will return a list, from there I convert the list to a table.

Thanks in advance

Re: API query return a range of selection

mahoneypat — Sat, 19 Jun 2021 22:33:09 GMT

Try taking out the Json.Document wrapper and just do Web.Contents to see what you get, and confirm/see the structure of the JSON. Share screen shots of what you get back (if you can), if the issue is not obvious to you.

Pat

Re: API query return a range of selection

v-yingjl — Tue, 22 Jun 2021 06:05:18 GMT

Hi clubspec ,

You can try to create a custom function like this and use it in the number table column:

(Page as text) => let Source = Json.Document(Web.Contents("https://csv.business.tomtom.com/extern?account=AAAc&username=XXX&password=PPP&apikey=818c28e9-09ec-4...objectno="&Page&"&outputformat=json")) in Source

let Source = List.Numbers(1,20,1), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), Custom1 = Table.TransformColumns(#"Changed Type", {"Column1", each Text.PadStart(_, 3, "0"), type text}), #"Invoked Custom Function" = Table.AddColumn(Custom1, "function2", each function2([Column1])) in #"Invoked Custom Function"

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