let
// Function to check if a page has actual data (not just headers)
PageHasData = (PageNumber as number) as logical =>
let
TargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/A?page=" & Number.ToText(PageNumber),
TryPage = try Web.BrowserContents(TargetURL),
Fallback = if TryPage[HasError] then null else TryPage[Value],
PageData = if Fallback = null then null else
Html.Table(Fallback, {{"Check", "TABLE.full-width-table > * > TR > :nth-child(1)"}}, [RowSelector = "TABLE.full-width-table > * > TR"]),
ValidRowCount = if PageData = null then 0 else Table.RowCount(PageData)
in
ValidRowCount > 1, // More than just the header row
// Function to find the last valid page dynamically
FindMaxPage = () =>
let
PageNumbers = List.Numbers(1, 20), // Check up to 20 pages
ValidPages = List.FirstN(PageNumbers, each PageHasData(_)) // Stop when a page is blank
in
if List.Count(ValidPages) = 0 then 1 else List.Last(ValidPages),
MaxPages = FindMaxPage(),
// Function to extract data from a page
GetPage = (PageNumber as number) =>
let
TargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/A?page=" & Number.ToText(PageNumber),
// Adding a delay of 1 second between requests
Sleep = Function.InvokeAfter(() => null, #duration(0, 0, 0, 0, 1)),
_ = Sleep,
TryPage = try Web.BrowserContents(TargetURL),
Fallback = if TryPage[HasError] then null else TryPage[Value],
PageData = if Fallback = null then null else
Html.Table(Fallback,
{
{"Name", "TABLE.full-width-table > * > TR > :nth-child(1)"},
{"DOB", "TABLE.full-width-table > * > TR > :nth-child(2)"},
{"Town", "TABLE.full-width-table > * > TR > :nth-child(3)"}
},
[RowSelector = "TABLE.full-width-table > * > TR"]
)
in
PageData,
// Generate only the valid pages dynamically
PageNumbers = List.Numbers(1, MaxPages),
// Fetch and process data from valid pages
AllPagesData = List.Transform(PageNumbers, each GetPage(_)),
CombinedData = List.RemoveNulls(AllPagesData),
AppendData = Table.Combine(CombinedData),
// Removing the first row (headers from the webpage)
#"Removed Top Rows" = Table.Skip(AppendData, 1),
// Filtering out unnecessary rows
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each not Text.Contains([Name], "Name (of dis")),
// Merging additional query data (assuming 'B' is another query to be merged)
#"Appended Query" = Table.Combine({#"Filtered Rows", B}),
// Changing DOB column to Date type
#"Changed Type" = Table.TransformColumnTypes(#"Appended Query", {{"DOB", type date}})
in
#"Changed Type"
