Forum Discussion

Abi224's avatar
Abi224
Frequent Visitor
3 years ago

How to get data from Rest Api using Power Query?

Hi Experts,

 

Hope you're doing well. I'm stuck at getting data from a rest API.

i'm having a survey link : "api.lm########/survey?page=1&limit=1000" , to get the data from this link i need to pass the following headers .

  1. Authorization 
  2. Consumer_id
  3. consumer_timestamp
  4. AUTH_signature
  5. service_name
  6. service_prod.

The highlighted headers(Consumer_timestamp,auth_signature) value will keep changing every minute for that reason we need to get the value from another link "lm######.register.com" then pass it to this link  "api.lm########/survey?page=1&limit=1000" .  This  link contain multiple pages ,i need all the pages in a single table.

 

let
Source1 = Json.Document(Web.Contents("lm######.register.com")),
timestamp = Record.Field(Source1,"epoch_time"),
auth = Record.Field(Source1,"auth_sign"),
source = Json.Document(Web.Contents("api.lm########/survey?page=1&limit=1000",[Headers=[Authorization="exy#########",Consumer_id="1234",consumer_timestamp=Number.ToText(timestamp),AUTH_signature=Text.From(auth),service_name="survey",service_prod="prod"]]))
in
Source

 

Regards,

Abiansh

 

 

 

Need Help

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Abi224 

     

    Do you know how many pages are approximately contained in this link? I guess you can probably change "page=1" to "page=2", "page=3" to get data from other pages? If so, you can try the solution introduced in the following blog. 

    Scrape Data from Multiple Web Pages with Power Query • My Online Training Hub

     

    The general idea is to convert your current query into a custom function with a page parameter. Generate a list of all page numbers and convert it into a table in another query. Then add a column to invoke the previous function. Provide the page column value for the page parameter. After querying all pages, expand the result table column to get all data in a single query. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Abi224's avatar
      Abi224
      Frequent Visitor
      3 years ago

      Hi v-jingzhang 

      I'm able to get data from other pages in a single table but the main problem i'm facing is the token, keep expiring every minute.

      I want to verify whether my M query is correct or not to get the token from lm######.register.com and pass it to api.lm########/survey?page=1&limit=1000

      let
      Source1 = Json.Document(Web.Contents("lm######.register.com")),
      timestamp = Record.Field(Source1,"epoch_time"),
      auth = Record.Field(Source1,"auth_sign"),
      source = Json.Document(Web.Contents("api.lm########/survey?page=1&limit=1000",[Headers=[Authorization="exy#########",Consumer_id="1234",consumer_timestamp=Number.ToText(timestamp),AUTH_signature=Text.From(auth),service_name="survey",service_prod="prod"]]))
      in
      Source

      Best Regards,

      Abinash