query not working - help

[Georgetimes1244]

hi, my query is not working. can you help?

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"

[v-zhangtin-msft]

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.