Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
magnus_b
Advocate II
Advocate II

Error handling when table is empty

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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

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? 

Mumiah_0-1683750652255.png

hopefully my explination made sense. 

magnus_b
Advocate II
Advocate II

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!

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

AlB
Community Champion
Community Champion

@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 

SU18_powerbi_badge

 

Anonymous
Not applicable

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


AlB
Community Champion
Community Champion

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 

 

SU18_powerbi_badge

 

Hi @AlB !

 

Here is the table returned in the first step:

Skjermbilde 2020-12-04 kl. 12.50.32.png

The second step returns this table:

Skjermbilde 2020-12-04 kl. 12.54.18.png

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:

Skjermbilde 2020-12-04 kl. 12.56.26.png

 

Thank you for your help, really appreciate it!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.