Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
02-25-2020 23:29 PM - last edited 03-30-2020 04:04 AM
Sometimes you are unlucky and the table you are refreshing is empty in the database.
In that case, often you don't want the refresh of the table to go through, but instead, have the refresh fail while keeping the old data in the Power BI model.
Normally, Power BI doesn't care if the table is empty or not, and will carry out the refresh and thereby truncate the table.
With the small addition of some M code and the Table.IsEmpty function, you can have Power BI evaluate if the table is empty and abort the refresh with an error message. That way the users will still be able to see old data, while you find the reason for the table to be empty.
All you need to do is insert the following line in the Advanced Query Editor:
EmptyCheck = if Table.IsEmpty(dbo_Employees) = true then error "Table is empty. Update aborted" else dbo_Employees
The function: Table.IsEmpty evaluates if the dbo_Employees table is empty (no rows), it will result in the error message below. If rows are detected the rest of the dbo_Employees table will be passed on to the next step.
Here is an example is the code embedded with the rest:
let
Source = Sql.Database("xxx.database.windows.net", "ABC"),
dbo_Employees = Source{[Schema="dbo",Item="Employees"]}[Data],
EmptyCheck = if Table.IsEmpty(dbo_Employees) = true then error "Table is empty. Update aborted" else dbo_Employees
in
EmptyCheck
I have tested on a Azure SQL database and SAP Business Warehouse and both returns an error if the table is empty.
This is great! What if I have several tables and I need to abort refresh only for thoses that are empty? If I use '...then error', PBI aborts refreshes for all the tables. I can't figure out how to re-write the query so only the empty tables' refreshes were aborted.