Forum Discussion

ashishrj's avatar
ashishrj
Power Participant
11 years ago

Data sorting issues in Power BI Designer based on Calculated columns

I have few fields that needs to be sorted based on some calculated columns and other fields. But it seems this works well in Excel Power Pivot but not in Designer sometimes. It displays command line for a sec and shows me a error messsage stating send a frown to Microsoft Team. This needs to be fixed in GA.

2 Replies

  • Georgetimes1244's avatar
    Georgetimes1244
    New Member
    1 year ago

    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"