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-nmadadi-msft]

Hi @Georgetimes1244 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks

[v-nmadadi-msft]

Hi @Georgetimes1244 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @Georgetimes1244 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

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.