API query return a range of selection

[clubspec]

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-4...objectno=001&outputformat=json"))

Thanks,

Raymond

[v-yingjl · Accepted solution]

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"

since it needs API key, currently it returns error

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.

[mahoneypat]

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

[clubspec]

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

[mahoneypat]

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