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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vinicius
Frequent Visitor

Conditional Logic - Looping Parameter page on API, Power BI

Hi,

 

I'm new in this M world.

So the problem is: I wanna increment the parameter page (++1), until the last page. In the future get the data on the page and go to the next page.

 

EX:

https://api.github.com/repos/" & login & "/" & repository & "/issues?state=all&sort=created&page=" & Text.From(page) // page is my variable

 

Thinking as JAVA man we just adding a variable and use for/while depending the situation.

Now, how to do that using M language?

 

I did the homework and this sites have some information:

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/03/10/conditional-logic-in-power...

https://seddryck.wordpress.com/2017/01/07/do-while-loop-with-power-bi-m-language/

 

But it's not working. 😞

 

Anyone can help me?

 

Thanks.

7 REPLIES 7
v-yuezhe-msft
Employee
Employee

@Vinicius,

What error message do you get? Please check if the following blogs help in your scenario.

Iterating over multiple pages of web data using Power Query
Power BI Using List.Generate to Reduce API Calls with Pagination


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

Thanks for help.

 

I used this method:

1 - Get Data>Web .... Conect API (to simplify the conection with header, parametres etc)
2 - Advanced Editor on this data and edit like this (Name = GetData):

(page as number) as table =>
let
Fonte = Json.Document(Web.Contents("https://api.powerbihelp.com.br/orders?page=" & Number.ToText(page) & "&per_page=100", [Headers=[Accept="application/json", ContentType="application/json", #"x-accountmanager-key"="", #"x-api-key"="", #"x-user-email"=""]])),
Data1 = Fonte{1}[Data],
RemoveBottom = Table.RemoveLastN(Data1,3)
in
Fonte

And creat a query blank:
List.Generate( ()=>
[Result= try GetData(1) otherwise null, Page = 1],
each [Result] <> null,
each [Result = try GetData([Page]+1) otherwise null, Page =[Page]+1],
each [Result])


In theory, my opnion, its correct. But nothing appears juts "List" in blank.

What I'm doing wrong?

Thanks.

@Vinicius,

Do you get correct result entering a page value in the following function?

(page as number) as table =>
let
Fonte = Json.Document(Web.Contents("https://api.powerbihelp.com.br/orders?page=" & Number.ToText(page) & "&per_page=100", [Headers=[Accept="application/json", ContentType="application/json", #"x-accountmanager-key"="", #"x-api-key"="", #"x-user-email"=""]])),
Data1 = Fonte{1}[Data],
RemoveBottom = Table.RemoveLastN(Data1,3)
in
Fonte


If you get correct result, add a list containing all pages, and add a custom column following the guide in  this blog.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft,

 

"If you get correct result, add a list containing all pages" you mean a table?

I'm using an API that limit the result, so I need to change the page.

I'm searching and testing ways to do it, but I don´t have enough knowledge.

I read the information on that link (/iterating-over-multiple-pages-of-web-data-using-power-query/) but the example used web page and I'm using API Json.

If I put Web.Page = StatusCode=-2146697208 DataSource.Error:

I'm trying this; https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

Thanks again.

@Vinicius,

In your scenario, firstly, add a new blank query in Power BI Desktop, then paste the following code in Advanced Editor of the blank query and check if you get a similar screenshot as follows.

(page as number) as table =>
let
Fonte = Json.Document(Web.Contents("https://api.powerbihelp.com.br/orders?page=" & Number.ToText(page) & "&per_page=100", [Headers=[Accept="application/json", ContentType="application/json", #"x-accountmanager-key"="", #"x-api-key"="", #"x-user-email"=""]])),
Data1 = Fonte{1}[Data],
RemoveBottom = Table.RemoveLastN(Data1,3)
in
RemoveBottom


1.JPG

Secondly, if you get a screenshot about function, just enter a page value(e.g.3) to invoke the function and check if it returns correct result.

Thirdly, if the above steps works, add a blank query in Power BI Desktop and create a list that contains all the values of your pages, then add custom column as described in this blog: https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/ .



Regards,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

I'm trying the first step and Power BI is showing:

Error in query ''. Expression.Error: We were unable to convert the value "https: //api.Teste.c ..." into type List.
Details:
    Value = https: //api.teste.com.br/orders?page = 3
    Type = Type

I'm using:

(page as number) as table =>
let

AuthKey = "",
Fonte = "https://api.test.com.br/orders?page=3", //I removed the parameter to change the page to test it

body = "{}",

Source = Json.Document(Web.Contents(Fonte,[

Headers = [#"Authorization"="",
#"x-api-key"="",
#"Content-Type"="application/json",
#"x-accountmanager-key"="",
#"x-user-email"="",
#"Accept"=""]
]
)),
Data1 = Fonte{1}[Data],
RemoveBottom = Table.RemoveLastN(Data1,3)
in
RemoveBottom

 

 

@Vinicius,

Please use the Web connector in Power BI Desktop to connect to a single page of your web, then post the code generated in Advanced Editor and post a screenshot of the data status in Query Editor.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors