Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

GET Request and Token authentication to download file as JSON

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 pro...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago
    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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