Forum Discussion

LON's avatar
LON
Helper I
9 years ago
Solved

Handling Empty Results

Hi   I have a query which returns a list of issues in jason format which I then transform into a table and visualize.  The problem is when no rows are returned i.e. no issues - so then there is no ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    LON,

    Change your code as follows. And maybe you would need to change List.IsEmpty(content) part in the following code to List.IsEmpty(Source) or List.IsEmpty(body), you can test it.

    let

Source = Json.Document(Web.Contents("https://xxxxxxxxxxxxx", [Headers=[zzzzz="xxxxxx"]])),
body = Source[body],
content = body[content],
#"Converted to Table" = Table.FromList(content, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Table1=Table.FromRecords({
        [Column1 = ""]
        
    }),
     result= if List.IsEmpty(content) then Table1 else #"Converted to Table" 
in
     result



    Regards,

Anonymous's avatar
Anonymous
Not applicable
9 years ago

LON,

Could you please share the query or sample data of your table for us to analyze? We also need to know where you perform the query.  

Regards,
Lydia

  • LON's avatar
    LON
    Helper I
    9 years ago

    Hi Lydia,

     

    Thanks very much for your help.

     

    Please see the images below - these are taken after the Convert to Table step in the Query Editor.

     

    In the first case there is data in the json but in the 2nd case there is no data returned.

     

    What I would like to do is to have a a table resulting with the same field names and so that the subsequent query steps will not result in an error (eg. column not found).

     

     

    I hope this makes sense - just let me know if you need more information

     

    Best Regards

     

    Liam

     

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      LON,

      Add new blank query in Power BI Desktop, paste the following code in the Advanced Editor of the blank query.

      let
    Source = Json.Document(File.Contents("YourPath\JSONFileName.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Table1=Table.FromRecords({
        [Column1 = ""]
        
    }),
     result= if List.IsEmpty(Source) then Table1 else #"Converted to Table" 
in
     result



      Regards,
      Lydia

      • LON's avatar
        LON
        Helper I
        9 years ago

        Hi Lydia,

         

        Thanks you so much for your help - I can see light at the end of the tunnel now!

         

        I wonder if you could advise please what the syntax would be for my query which gets the json from a web query?

        i.e.

         

        Source = Json.Document(Web.Contents("https://xxxxxxxxxxxxx", [Headers=[zzzzz="xxxxxx"]])),
        body = Source[body],
        content = body[content],
        #"Converted to Table" = Table.FromList(content, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

         

        Thanks again and Best Regards

         

        Liam