cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Vinicius Frequent Visitor
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. Smiley Sad

 

Anyone can help me?

 

Thanks.

7 REPLIES 7
Moderator v-yuezhe-msft
Moderator

Re: Conditional Logic - Looping Parameter page on API, Power BI

@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.
Vinicius Frequent Visitor
Frequent Visitor

Re: Conditional Logic - Looping Parameter page on API, Power BI

@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.

Moderator v-yuezhe-msft
Moderator

Re: Conditional Logic - Looping Parameter page on API, Power BI

@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.
Vinicius Frequent Visitor
Frequent Visitor

Re: Conditional Logic - Looping Parameter page on API, Power BI

@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.

Moderator v-yuezhe-msft
Moderator

Re: Conditional Logic - Looping Parameter page on API, Power BI

@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.
Vinicius Frequent Visitor
Frequent Visitor

Re: Conditional Logic - Looping Parameter page on API, Power BI

@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

 

 

Moderator v-yuezhe-msft
Moderator

Re: Conditional Logic - Looping Parameter page on API, Power BI

@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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 344 members 3,489 guests
Please welcome our newest community members: