Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Abi224
Frequent Visitor

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

 

 

 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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.

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Kudoed Authors