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.
Hello Community,
I have very minimal knowledge on APIs . I got a request from one of the clients to design a report . However, they said we should get data from JSON as data source. They just provided me a link as shown in screenshot.
They said We need to Post a GET command and also provided a Token for authentication purpose. By downloading file in JSON format , we can get use data option from Power BI for JSON and use data.
However, I am not able to download file . Could some one please suggest how to get data from the link and token code provided below .''
Solved! Go to Solution.
Query parameters and header information can be part of the Web.Contents call. The last URL shows the examples.
https://docs.microsoft.com/en-us/powerquery-m/web-contents
https://docs.microsoft.com/en-us/power-query/web.contents
https://kohera.be/blog/power-bi/the-hidden-gems-of-the-function-web-contents/
Hi @Anonymous,
You can try to use the following code if it meets your requirement:
let
GetJson = Web.Contents(
"https://xxxxxxxx/api",
[
RelativePath="/v1.0/data/search",
Query=
[
query="xxxxxxx",
limit=1000
]
]
),
FormatAsJson = Json.Document(GetJson)
in
FormatAsJson
Reference links:
Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code
Get Data from Twitter API with Power Query
Regards,
Xiaoxin Sheng
in Power Query use the "from Web" data source dialog which translates into Web.Contents(<url>,[<parameters]). Once you have the raw data you can open it with JSON.Document() etc.
"We need to Post a GET command" - this made me chuckle - thank you.
Hi @Anonymous,
You can try to use the following code if it meets your requirement:
let
GetJson = Web.Contents(
"https://xxxxxxxx/api",
[
RelativePath="/v1.0/data/search",
Query=
[
query="xxxxxxx",
limit=1000
]
]
),
FormatAsJson = Json.Document(GetJson)
in
FormatAsJson
Reference links:
Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code
Get Data from Twitter API with Power Query
Regards,
Xiaoxin Sheng
Hi ,
This is what i am actually looking for. This worked for me. Thanks a lot and sorry for late reply.
There is a limit of 1000 records. But we have around 2300 records . So how do we neeed to execute the next statement to get remaining records . I tried something but it isnt working . Could you please help me ? Below is the M-query i am using to get all the records and not just 1000 records.
let
Source = Json.Document(Web.Contents("URL", [Headers=[Accept="application\json", Authorization="Bearer myToken"]])),
#"ExplodeTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"ExpandResults" = Table.ExpandRecordColumn(#"ExplodeTable"[results], "Column1", {"Name", "Software Type"}, {"Name", "Software Type"}),
#"GatherNext" = Table.AddColumn(#"ExplodeTable", "NextValue", each if #"ExplodeTable".[Next] <> null then Json.Document(Web.Contents(#"ExplodeTable"[Next], [Headers=[Accept="application/json", Authorization="Bearer Mytoken"]]) else null)
in
#"GatherNext"
Thanks,
Venkatesh
Your problem is not the [next] statement but the Source statement. Either use a recursive function to collect all headers, or if you know the list size then make three calls, with offset 0, 1000, and 2000, and merge the results.
Query parameters and header information can be part of the Web.Contents call. The last URL shows the examples.
https://docs.microsoft.com/en-us/powerquery-m/web-contents
https://docs.microsoft.com/en-us/power-query/web.contents
https://kohera.be/blog/power-bi/the-hidden-gems-of-the-function-web-contents/
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.
User | Count |
---|---|
16 | |
2 | |
2 | |
1 | |
1 |