Forum Discussion

clubspec's avatar
clubspec
Helper III
5 years ago
Solved

API query return a range of selection

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...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
clubspec
Helper III
5 years ago

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's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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