How to get data from Rest Api using Power Query?

[Abi224]

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

[v-jingzhang]

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]

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