GET Request and Token authentication to download file as JSON

[Anonymous]

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

 

[lbendlin · Accepted 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/

[lbendlin]

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.

[Anonymous]

Hi, Thanks for the help. I knew that my question will make you laugh. But i am picking this up for the first time. I managed to insert the URL and this is hw it looks. let Source = Web.BrowserContents("https://addm-dc.lowes.com/") in Source Could you please help me how to provide token information as well ? I am looking for M-query similar to the code shown above.

[Anonymous · Accepted solution]

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

[Anonymous]

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 

[lbendlin]

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.

[lbendlin · Accepted 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/