Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.