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.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

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.

4 Replies

  • 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

         

  • v-yingjl's avatar
    v-yingjl
    Community Support
    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.