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

Hi LON

 

That depends on what is missing. Is there a date-column in your data?

  • LON's avatar
    LON
    Helper I
    9 years ago

    Hi Anonymous

     

    Thanks very much for getting in touch so quickly....

     

    I checked the dat and there is no date column.  

     

    The report works fine when there is data retirned which is then converted from json into a table but when there is no data returned by the query there is no table created in that step.   Hope this helps explain.

     

    Best Regards

     

    Liam

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      LON

       

       

      The reason I'm asking is you probably need to make a new table to have the ability to show 0's for rows where there is no data. The solution to this depends on whether you have date-data or not.

       

      If you have date-data, I would try to create a seperate date table and then create a relation between them in order to display blanks or zeros. You could try a similar method for something else than dates. Let me know if this makes sense. I'll gladly elaborate for you.

       

      Best,

      Martin

      • LON's avatar
        LON
        Helper I
        9 years ago

        Hi Anonymous

         

        Thanks - yes  it would be great if you could elaborate please..

         

        Would the idea  be something like having another table with a zero value in it and having the report take that value and display it if the query did not return any data and hence there was no table created from the json (ie when there were no issues found by the query)?

         

        Best Regards

         

        Liam