Forum Discussion

Shijin's avatar
Shijin
Frequent Visitor
2 years ago

Power BI Desktop Table is empty

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,

   

 

 

Need Help

5 Replies

  • Shijin's avatar
    Shijin
    Frequent Visitor
    2 years ago

    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

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Shijin
        Frequent Visitor
        2 years ago

        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