Power BI Desktop Table is empty

[Shijin]

Hi,

   Iam new to the Power BI. I have created a Power BI Report for getting the branch list based on the zip code. This query connecting to a website and getting the inforamtion based on zip code. This app working as expected, but the issue is some time iam getting the result as blank showing this tabe is empty. Once again if i refresh then, the result i will be appreared. somtimes i need to refresh manytimes based on the input count.

My question that, is there any way i can re-run the query, if the table is blank or null. i am expecting th result as, check the table if blank and then refresh it again untill we have the values.

This is the sample from my query.

This is the empty error is getting message usually.

Once i refresh the query again and some time again. i will get the result as expected.

If anyone can help be how to write the query inside to check whether the table have blank value then try source in again from the link.

I really apprciate your support and thanks in advance.

Thanks,

[Shijin]

Hi all.

   If in case my question was not clear. i want to simplify that question.

Actaully iam looking for something like this query below. If Source data table is empty or blank, then i want to source the data again and if table is not empty, then pass it.

The below query syntax is not correct, but i want to show an example that iam expecting somthing like this.

let
Source = Web.Contents("https://www.xxxxxxxx.com/us/en/store-finder/find?query=45505"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".name-address.col-3 > .name"}, {"Column2", ".name-address.col-3 > .address"}}, [RowSelector=".name-address.col-3 > .name"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
#"Kept First Rows" = Table.FirstN(#"Changed Type",1)

Result = if Table.IsEmpty(#"Extracted Table From Html") then Source
else #"Extracted Table From Html"

in
Result

Thanks in advance!

Thanks,

Shijin

[v-shex-msft]

HI @Shijin,

Perhaps you can try to use the following M query formula, I define a recursion custom function to looping getting data, it will processing until the result table is not blank or exceed to the retry times:

let
    LoopGetData = (initCount as number) as table =>
        let
            URL = "https://www.xxxxxxxx.com/us/en/store-finder/find?query=45505",
            Source = Web.Contents(URL),
            extracted = Html.Table(
                Source,
                {{"Column1", ".name-address.col-3 > .name"}, {"Column2", ".name-address.col-3 > .address"}},
                [
                    RowSelector = ".name-address.col-3 > .name"
                ]
            ),
            #"Changed Type" = Table.TransformColumnTypes(extracted, {{"Column1", type text}, {"Column2", type text}}),
            filtered = Table.FirstN(#"Changed Type", 1),
            output =
                if initCount > 0 then
                    if Table.RowCount(filtered) > 0 then
                        filtered
                    else
                        @LoopGetData(URL, initCount - 1)
                else
                    filtered
        in
            output,
    Result = LoopGetData(5)
in
    Result

Regards,

Xiaoxin Sheng

[Shijin]

Hi @Xiaoxin Shen,

Thank you for your prompt response.

I have tried with that M query formula, but iam getting this below error message.

Can you please help me with this error.

Thanks in advance!

Thanks,

Shijin

[v-shex-msft]

HI @Shijin,

I'm sorry, it seems like I haven't correctly shared the latest version codes. It seems like the code include the old version custom function usages and it conflicts with the new version to cause the issues.(old version function parameterized the URL that used to getting data)

Please take a look at the following codes to confirm if it works on your side:

let
    LoopGetData = (initCount as number) as table =>
        let
            URL = "https://www.xxxxxxxx.com/us/en/store-finder/find?query=45505",
            Source = Web.Contents(URL),
            extracted = Html.Table(
                Source,
                {{"Column1", ".name-address.col-3 > .name"}, {"Column2", ".name-address.col-3 > .address"}},
                [
                    RowSelector = ".name-address.col-3 > .name"
                ]
            ),
            #"Changed Type" = Table.TransformColumnTypes(extracted, {{"Column1", type text}, {"Column2", type text}}),
            filtered = Table.FirstN(#"Changed Type", 1),
            output =
                if initCount > 0 then
                    if Table.RowCount(filtered) > 0 then
                        filtered
                    else
                        @LoopGetData(initCount - 1)
                else
                    filtered
        in
            output,
    Result = LoopGetData(5)
in
    Result

Regards,

Xiaoxin Sheng

[Shijin]

Hi @Xiaoxin Shen,

 Thank you for this code correction. Now i dont find any error and the code is executed well.

But iam still finding the issue with the blank cells. After mutiple refresh i am able to get the complete data. On the first refresh, iam still finding the null or blank values.

I have attached below the sample result.

Please have a look and advice me on this. 

Thanks for your support!

Thanks,

Shijin