Handling Empty Results

[LON]

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 table to visualise.       What I would like to do is display a "zero issues" card or whatever in my report.  

Hope you can help please?

[Anonymous]

@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]

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]

@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]

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

[Anonymous · Accepted solution]

@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,

[mauryavijai]

This is not working. In my case from Source = Json.Document(Web.Contents("https://api.abcxyz.com/v2/data_check?request_key=abcxyz12345")) is not giving any results. 

is there any way to handle the no result so that we can avoid the "DataFormat.Error: We reached the end of the buffer." on the PowerBI model refresh. 

we can show blank data. 

[LON]

Hi Lydia

Just a follow up question please - I tested that and it worked perfectly thanks with the #"Converted to Table".   Im just a bit stuck in trying to include a second statement where I expand the Table - basically how to put multiple statements in the if .. then.. else statement?

#"Converted to Table" = Table.FromList(content, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",

Best Regards

Liam

[Anonymous]

@LON,

Please refer to the following blog about if...then...else statement.

https://community.powerbi.com/t5/Community-Blog/Conditional-Code-Branching-in-Power-BI-Query-if-then...

Regards,
Lydia

[LON]

Thanks very much for al your help with this - very much appreciated.

Best Regards

Liam

[Anonymous]

Hi @LON

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

[LON]

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]

@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]

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

[Anonymous]

That could be one approach.

I'm not too sure what will work as I'm not sitting directly with your problem, but the approach would be to insert 0 when nothing is found. I hope this makes sense.

[LON]

Thanks very much Martin - Im learning so much from this forum.

Best Regards

Liam