query not working - help

Hi, Georgetimes1244 

You can try the following methods.

let
    PageHasData = (PageNumber as number) as logical =>
    let
        TargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/A?page=" & Text.From(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:not(:only-child) > td:nth-child(1)"}}, [RowSelector = "TABLE.full-width-table tr"]),
        ValidRowCount = if PageData = null then 0 else Table.RowCount(PageData)
    in
        ValidRowCount > 0, // Adjust based on actual header rows

    FindMaxPage = () =>
    let
        PageNumbers = List.Numbers(1, 20),
        ValidPages = List.Select(PageNumbers, each PageHasData(_)),
        MaxPage = if List.IsEmpty(ValidPages) then 0 else List.Max(ValidPages)
    in
        MaxPage,
 
    MaxPages = FindMaxPage(),

    GetPage = (PageNumber as number) =>
    let
        TargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/A?page=" & Text.From(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, {
                {"Name", "TABLE.full-width-table tr > td:nth-child(1)"},
                {"DOB", "TABLE.full-width-table tr > td:nth-child(2)"},
                {"Town", "TABLE.full-width-table tr > td:nth-child(3)"}
            }, [RowSelector = "TABLE.full-width-table tr:not(:has(th))"]) // Skip header rows
    in
        PageData,

    PageNumbers = if MaxPages = 0 then {} else List.Numbers(1, MaxPages),
    AllPagesData = List.Transform(PageNumbers, each GetPage(_)),
    CombinedData = Table.Combine(List.RemoveNulls(AllPagesData)),
 
    #"Filtered Rows" = Table.SelectRows(CombinedData, each [Name] <> null and [Name] <> "Name (of disqualified person)"),
 
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"DOB", type date}}, "en-GB")
in
    #"Changed Type"

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.