Howdy folks. I've got a PowerQuery that pulls data from a paginated URL back into Excel for analysis. The general approach I'm taking is from the this post at the datachant blog blog, with multilpe pages of results being obtained as per this post at Matt Masson's blog.
Only problem is, Matt's approach hard-codes the number of pages that actually exists, and if you feed it a bigger number to handle the possiblity of growth, then you get an error.
I'd like to be able to feed the function an oversized number of pages, so that I automatically capture any growth in pages going forward. Is there any way I can restructure the below wrapper code, so that I can say call the function with page parameters from 1 to 100, and just simply ignore any errors that are generated when I exceed the actual amount of pages currently available?
let
Source = {1..13},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "GetData", each GetData([Page], 143382)),
#"Expanded GetData" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetData", {"Category", "Comments", "Title", "Votes"}, {"GetData.Category", "GetData.Comments", "GetData.Title", "GetData.Votes"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded GetData",{{"GetData.Category", "Category"}, {"GetData.Comments", "Comments"}, {"GetData.Title", "Title"}, {"GetData.Votes", "Votes"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Page", Int64.Type}, {"Comments", Int64.Type}, {"Votes", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Votes", Order.Descending}})
in
#"Sorted Rows"I've found a number of resources that are likely helpful, but my M isn't currently good enough to understand how to generalise the approaches. I'm guessing it's as simple as sneaking a try either in the wrapper, or in the actuual navigation steps in the function itself is the way forward, but at this stage I'm just blindly adding stuff to no avail.
I found some google hits that looked like they might be suggetsing a way forward, but I'm just too new at M to translate them to address my own requirements:
https://blog.crossjoin.co.uk/2016/08/09/handling-404-not-found-errors-with-web-contents-in-power-que...
https://social.technet.microsoft.com/Forums/en-US/39537117-40c5-4ca8-8b6e-f64674342c53/error-handlin...
https://community.powerbi.com/t5/Desktop/Error-handling-in-loading-files-from-folder/m-p/297082#M131...
