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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

9 REPLIES 9
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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
Super User
Super User

@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

 

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
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
Super User
Super User

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors