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,

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

Anonymous's avatar
Anonymous
Not applicable
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,

  • LON's avatar
    LON
    Helper I
    9 years ago

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

     

    Best Regards

     

    Liam

  • LON's avatar
    LON
    Helper I
    9 years ago

    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

  • mauryavijai's avatar
    mauryavijai
    Frequent Visitor
    5 years ago

    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.