List.generate with web.contents

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

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.