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. ...
Need Help
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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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