Error handling when table is empty

[magnus_b]

Hi!

I am using direct query against an API, that will return a table with a row for each visitor today. At the beginning of the day, there are no rows in this table, so the query will produce an error. I have tried multiple approaches for handling the errors, but have had no luck so far. Any advice?

Here is the script:

let    Source = CdataRest.DataSource("API", "URI=https://api.example.com/visits/export?type=json&fromTime="&Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")&"&toTime=" & Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd"), [Query=null]),    REST_Schema = Source{[Name="REST",Kind="Schema"]}[Data],    locations_Table = REST_Schema{[Name="locations",Kind="Table"]}[Data],    #"Filtered Rows" = Table.SelectRows(locations_Table, each true),    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",null,0,Replacer.ReplaceValue,{"originalPosition"}),    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1","[""","",Replacer.ReplaceText,{"serviceIds"}),    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","""]","",Replacer.ReplaceText,{"serviceIds"}),    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,0,Replacer.ReplaceValue,{"waitDuration"})in    #"Replaced Value3"

If the table is empty I get the following errors:

- Step 3 (locations_Table) -> DataSource.Error: The table has no visible columns and cannot be queried.

- Step 5 (Replaced Value1) -> Expression.Error: The column 'originalPosition' of the table wasn't found.

- Step 6, 7 and 8 produces a similar error to step 5

[watkinnc · Accepted solution]

Try this:

= Table.AddColumn(PriorStepOrTableName, "TableToExpand", each if Table.IsEmpty(_[Data]) then Table.FromColumns({{null}, {null}, {null}, {null}}, {"Column1", "Column2", "Column3", "Column4"}) else [Data])

Now if the table is empty, you'll get a 4 column, 1 row table of null values.  Make as many nulls and column names as you need.  You should replace my "Column1" names with the actual column names.

--Nate

[Mumiah]

Hi Watkinnc, I'm trying to implement your solution but unfortunately it's a little different for me, I pull the data from a different system and will always get this result when there's "no data", usually if there is data it adds the column headers on the forth row and I proceed to formatting the whole table, but what would be your suggestion on this case? 

hopefully my explination made sense. 

[magnus_b]

After trying all the different approaches suggested here I was still not able to solve the issue. Strangely it seems like try... otherwise would not catch this specific error. I have seen this being referenced to as a bug on other forums.

The solution ended up being retrieving two days of data from the API, instead of just today, ensuring that the returned table is never empty.

Thanks for all the help!

[Mumiah]

Thanks for that great suggestion of just getting more data to ensure no empty tables! 

[AlB]

@magnus_b 

I was looking to get the real table data rather than just the result of the first step. I didn't explain myself properly. Perhaps you can use the try ... otherwise .... construct to catch the errors and return an alternative result in case they occur

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[watkinnc]

Whatever step causes that error, and it's difficult to tell without seeing your steps, wrap like this:

= if Table.IsEmpty(HereIsYourCurrentExpressionThatGivesErrorsWhenEmpty) then Table.FromRecords({[Column1 = "SomeText", Column2 = "SomeOtherText", Column3 = "MoreText"]}, {"Column1", "Column2", "Column3"}) else HereIsYourCurrentExpressionThatGivesErrorsWhenEmpty

Basically, If this expression returns an empty table than make a table like so.

---Nate

[mahoneypat]

You can work some if then else logic in at the step level with Table.RowCount of your Table, and add a dummy table if it is 0 so that your later steps don't have errors.

Regards,

Pat

[AlB]

Hi @magnus_b 

Can you provide the table that you get in the first step, Source?  The M code provided does not work on my side  (I guess I need to install the connector or similar)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[magnus_b]

Hi @AlB !

Here is the table returned in the first step:

The second step returns this table:

And the problem occurs if the "Table" in column Data in step 2 is empty. You can see the error I get here, when I try to view the table in column data in step 2:

Thank you for your help, really appreciate it!