cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
scastrontu
Resolver I
Resolver I

List.generate with web.contents

Hi all,

 

I hope you can help me.

 

I am trying to use List.Generate to retrieve the number of pages my organisational page has. From each page I want to extract the records from the field [Results].

 

I start retrieving page 0 https://photos.app.goo.gl/c6kfDYViNomNuoVT7. I know Page 11 is the last one because Page 11 doesn't have the field [Next] https://photos.app.goo.gl/RDfEpCHMQNKc1GBU6. So, for now I have a query for each of the 11 pages and combine all results in one single table. I guess the script has to add 1 until [Next] doesn´t exist.

 

I got the following script following the BI Account blog https://www.thebiccountant.com/2020/05/15/miss-last-page-paging-power-bi-power-query/, so maybe @ImkeF can help me here.

 

List.Generate ( ()=>
[Result = Json.Document(Web.Contents("https://x/api/4.2/selections/3"))],
each [Result][Next] <> null,
each [Result = Json.Document(Web.Contents(Text.Combine({"x/api/4.2/selections/3", Text.Replace([Result][Next], "?cursor=", "/")})))]
)

 

However it gives an error https://photos.app.goo.gl/EXdPk8vVHT3Ndw2Y7

 

Thanks for the help.

1 ACCEPTED SOLUTION
scastrontu
Resolver I
Resolver I

Hi, 

I thought I would share the solution. Again, thanks for your help.

 

let
GetData =
(PagePath as number) =>
let
Source =
Json.Document(Web.Contents("x",

[
RelativePath =
Text.Combine(
{
"/",
Text.From(PagePath)
}
)
]
))
in
Source,
Source =
List.Generate(
() =>
[
page = 0,
Page = GetData(page)
],
each not List.IsEmpty([Page][Results]),
each
[
page = [page] + 1,
Page = GetData(page)
]
),

View solution in original post

15 REPLIES 15
scastrontu
Resolver I
Resolver I

Hi, 

I thought I would share the solution. Again, thanks for your help.

 

let
GetData =
(PagePath as number) =>
let
Source =
Json.Document(Web.Contents("x",

[
RelativePath =
Text.Combine(
{
"/",
Text.From(PagePath)
}
)
]
))
in
Source,
Source =
List.Generate(
() =>
[
page = 0,
Page = GetData(page)
],
each not List.IsEmpty([Page][Results]),
each
[
page = [page] + 1,
Page = GetData(page)
]
),

PhilipTreacy
Super User
Super User

Hi @scastrontu 

If you click the word Error it will show you the error message.

If you click beside the word Error it will show you the contents of the error message on the bottom of your screen.

When you try my code and get a list of records, what is in the records? Does my code ever stop?  If it does then that indicates it is paging through the API correctly.  What is in the last Record?  Is there a Next field?  Without actually having access to the API it's hard to be certain what is going on.

As with the error, you can click beside the word Record to see the contents of that Record.

You initially stated that you wanted the Record from the results but I think you are actually after the Lists from the results.  List and Record have specific meanings in Power Query.   So this code is more likely to give you what you want.

 

let
    url = "https://x/",
    path = "api/4.2/selections/3",
    Source = List.Generate ( 
        
                ()=> [ Result = Json.Document(Web.Contents(url & path)) ],

                each [Result][Next] <> null,

                each [Result = Json.Document(Web.Contents(url & [Result][Next]))],

                each [Result][Results]

)
 
in
    Source

 

This should give you a list (column) of Lists, which are the contents of the Results field in the returned Web.Contents Result.

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Philip,

 

thanks for getting back to me. Yes, you're right, I want the lists. Sorry if this confused you, I'm not very good with M... I think both scripts are good at accessing the records from page 1, but it looks like they don't move to next pages. See below.

 

Both of your proposals gave a lot of results, millions. Both were fine in terms of providing the records I wanted (although the second script was better with less steps needed than the first script). However, both proposals give millions of rows. Each row represents a project, and I know I should have less than 1100 projects (100 projects per page, and I have 11 pages on my website).

 

Also I can tell both scripts are only retrieving results from page 0 https://photos.app.goo.gl/uYd8C6LqcwKHttq7A and  don't move to page 1, etc. until page 11 (when there's no Next field https://photos.app.goo.gl/g8ChgZ76F3ECnkTa6 ). In Script 1 I get a step where I can see all the fields from the json file, see the json fiel fields and notice it's always retrieving from page 0. Where in the second script I don't have a stept to see the json fields, but I see that rows from row 101 are repeated (row 101 is the same value as row 1, and so on). So, looks like the script doesn't move to the next page .

 

Script 1 https://photos.app.goo.gl/SqK1yBSdT9cdo51k6 (see a row for each project. Each page has a List, which has 100 values, each a project).

 

let
url = "https://x",
path = "api/4.2/selections/3",
Source = List.Generate (

()=> [ Result = Json.Document(Web.Contents(url & path)) ],

each [Result][Next] <> null,

each [Result = Json.Document(Web.Contents(url & [Result][Next]))]

),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result"}, {"Result"}),
#"Expanded Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Result", {"Results"}, {"Results"}),
#"Expanded Results" = Table.ExpandListColumn(#"Expanded Result", "Results"),
#"Expanded Results1" = Table.ExpandRecordColumn(#"Expanded Results", "Results", {"_self"}, {"_self"}),
#"Expanded _self" = Table.ExpandRecordColumn(#"Expanded Results1", "_self", {"APIURL"}, {"APIURL"})
in
#"Expanded _self"

 

Script 2 https://photos.app.goo.gl/ufcbCyx1viPUDDDw8 

 

let
url = "https://x/",
path = "api/4.2/selections/3",
Source = List.Generate (
()=> [ Result = Json.Document(Web.Contents(url & path)) ],
each [Result][Next] <> null,
each [Result = Json.Document(Web.Contents(url & [Result][Next]))],
each [Result][Results]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"_self"}, {"Column1._self"}),
#"Expanded Column1._self" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1._self", {"APIURL"}, {"Column1._self.APIURL"})
in
#"Expanded Column1._self"

 

Sorry I can't give you the website or password to check this... as mentioned, it's a business page and can't share this info. Thanks again for your time and helping me.

 

Best.

 

PhilipTreacy
Super User
Super User

Hi @scastrontu 

What does the error say?

I can't test your code but in case there's an error in the code, try this which is a little rearranged

 

let
    url = "https://x/",
    path = "api/4.2/selections/3",
    Source = List.Generate ( 
        
                ()=> [ Result = Json.Document(Web.Contents(url & path)) ],

                each [Result][Next] <> null,

                each [Result = Json.Document(Web.Contents(url & [Result][Next]))]

)
 
in
    Source

 

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Philip,

 

The error I got with my solution is this https://photos.app.goo.gl/DaexpS7R8kGSxWBK9.

Thanks for the response. Your script provides endless number of rows https://photos.app.goo.gl/mVbzeWshJac4MW1SA

 

Thanks.

Rocco_sprmnt21
Community Champion
Community Champion

 

 

 

 

 

Ciao

dovresti far vedere nel dettaglio il tipo di errore che risulta, come dovresti pure postare il codice che usi (magari "nascondendo" le parti sensibili).

Ad occhio, farei un tentativo sul controllo di fine iterazione:

each [Result][Next] <> null,


Se ho cpaito bene alla fine il field [Next] manca del tutto non è semplicemente null.


Prova a cambiare l'istruzione, con qualcosa del gerene:

Record.HasFields(

Ma, ripeto, è solo una congettura. Con le informazioni che hai fornito è difficile fare una diagnosi

 

Hi Rocco_sprmnt21,

 

Thanks for the help. Your answer is in Italian, so I hope Google Translator did a good job. 

The error give this detail The error I got with my solution is this https://photos.app.goo.gl/DaexpS7R8kGSxWBK9. I'm afraid I did put all info I have, don't have anything else to share with except the website, which is confidential. 

I'm not very skilled with M... what do you suggest for Record.hasfield? I did search on google but don't know how to make this formula for my case.

 

Thanks.

 

List.Generate ( ()=>
[Result = Json.Document(Web.Contents("x/api/4.2/selections/3"))],
each Record.HasFields([Next]),
each [Result = Json.Document(Web.Contents(Text.Combine({"https://x/api/4.2/selections/3", Text.Replace([Result][Next], "?cursor=", "/")})))]
)

Hi,

your google is no worse than mine so ... 😁

 

About the error message you should click on the cell where there is error to see the error log.

 

The sintax of record.hasfields function is  here 

 

should (if you con't share your code, we can only suppose)be like this:

 

each Record.HasFields(Result, "Next"),

or better

 

each Record.HasFields([Result], "Next"),

 

 

hahaha. Yep, same google translator hahaha

 

Yes, I looked at that syntax but couldn't come up with a solution...

I used your suggestion but gave me an error: An error occurred in the ‘’ query. Expression.Error: The name 'Results' wasn't recognized. Make sure it's spelled correctly.

So, I used the square brackets

 

List.Generate ( ()=>
[Result = Json.Document(Web.Contents("https://x/api/4.2/selections/3"))],
each Record.HasFields([Results], "Next"),
each [Result = Json.Document(Web.Contents(Text.Combine({"https://x/api/4.2/selections/3", Text.Replace([Result][Next], "?cursor=", "/")})))]
)

 

But gave an error 

An error occurred in the ‘’ query. Expression.Error: The field 'Results' of the record wasn't found.
Details:
Result=[Record]

 

Thanks.

Why each Record.HasFields([Results], "Next")?

 

I wrote

each Record.HasFields([Result], "Next"),

 

wihout final s.

Yes, sorry, forgot to mention that. I tried without the "s" but gave an error: 

An error occurred in the ‘’ query. Expression.Error: The name 'Result' wasn't recognized. Make sure it's spelled correctly. And if I use [Result], I get this https://photos.app.goo.gl/DaexpS7R8kGSxWBK9

 

When I call a page I get the field "Results" instead of "Result". That's why I used Results instead. But, either I use square brackets or not, it doesn't work.

 

Thanks.

"An error occurred in the ‘’ query. Expression.Error: The name 'Result' wasn't recognized. Make sure it's spelled correctly. And if I use [Result], I get this https://photos.app.goo.gl/DaexpS7R8kGSxWBK9"

 

1) click on the error cell to see the complete description

2) open advanced editor and copy and paste the code you use (with the omissis eventually)

Hi Rocco,

 

All info I get shows in the link I gave https://photos.app.goo.gl/DaexpS7R8kGSxWBK9.

So I get just one record as "Error", and If I click it, at the bottom of the picture you'll see a row with two columns: Result, Record.

 

The script is this

List.Generate ( ()=>
[Result = Json.Document(Web.Contents("https://x/api/4.2/selections/3"))],
each Record.HasFields([Result], "Next"),
each [Result = Json.Document(Web.Contents(Text.Combine({"https://x/api/4.2/selections/3", Text.Replace([Result][Next], "?cursor=", "/")})))]
)

 

As mentioned, either I use Result, [Result], Results, [Results]. Nothing works, all of them give errors I showed in previuos posts.

 

Thanks for the help and time.

 

 

"So I get just one record as "Error", and If I click it, at the bottom of the picture you'll see a row with two columns: Result, Record."

then the problem isn't anymore the Result parameter.

you have to click on the word error and when get the new window with Result=record click on the word record to see what contains and so on until you get the error log.

I'm sorry I can't help you more.

 

Hi Rocco,

 

If I click "Error" I get this https://photos.app.goo.gl/DsNTtW47bLjteEsRA

Then, If I click Record I get this https://photos.app.goo.gl/rQdVyxjg1VkyJoND8

 

Weird, I get the word "Error", but If click on that then I get the results of calling the website (first page)

 

Thanks.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Kudoed Authors