cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Error on Scheduled Refresh: Table contains blank values

I have a dashboard scheduled to refresh daily, but each time I get an error that says:

 

"Column in Table contains blank values and this is not allowed for columns on the one side of many-to-one relatioship for coulmns that are used as the primary key of a table."

 

I went to this table and edited the query to Remove Blank Rows, but I still get the error every time I try to refresh the data.

 

Anyone have any suggestions or know what the problem is?

 

 

 

16 REPLIES 16
Highlighted
Helper IV
Helper IV

Re: Error on Scheduled Refresh: Table contains blank values

Experienced this this morning for some reason.

 

Try going to the table referenced in the Error message, then sort your columns in either ascending/descending order. Like me, you may find that your column does actually contain a blank. (!)

 

Hope that helps!

Highlighted
Super User IX
Super User IX

Re: Error on Scheduled Refresh: Table contains blank values

Select the column that is part of the one-to-many relationship. Choose "Replace values". For the value to find, don't type anything. For the replacement value, type "BLANK" or something depending on data type. This will replace blank values in the column with whatever you put in. Then you should not have any real blanks.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
New Member

Re: Error on Scheduled Refresh: Table contains blank values

I am also facing same error when refreshing data, were you able to find any solution/workaround to the problem.

Kindly share.

As suggested I replaced blank values with BLANK but that did not solve anything,

Highlighted
New Member

Re: Error on Scheduled Refresh: Table contains blank values

Hi,

 

Having the same issue, replaced all the blanks with "BLANK" string. Verified that there are no empty values.

However the scheduled refresh fails with the same error.

 

Have anyone found a solution/workaround?

 

Thanks,

Alex.

Highlighted
Advocate I
Advocate I

Re: Error on Scheduled Refresh: Table contains blank values

 

I found on the PowerBI desktop application

 

1. Click on "Edit Queries" 
1.jpg

 

 

 

 

 

 

Step 2 - Select the table that has the error.

 

2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 3 - Select column which gives the error and in drop down filter select "Remove Empty" 

3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Close and Apply and Refresh again.

 

This solved my problem and addressed all my refresh errors through this.

 

Excuse for any typos or errors, this is first time i am posting a solution, I am usually on the other end, but for this realized there are many people like me. Smiley Happy

Highlighted
Advocate II
Advocate II

Re: Error on Scheduled Refresh: Table contains blank values

EDIT:  I recreated the calendar table in power bi desktop, built everything identical, works fine. 

The difference is that the old one was converted from a excel file.. 

 

 

 

This did not work for me. I get the error on my calendar table and a autogenerated calendar doesn't have any blanks of course. 

The report works just fine in power bi desktop, but when refreshing online in powerbi.com i get the error.. strange.. 

 

Error message: http://imgur.com/a/ZonnT

Data model: http://imgur.com/a/Kixyw  

 

Just to really confirm to you guys that i don't have blanks: The calendar table has 366 rows, which is correct for a 2016 table. 

I double checked that the "Many" side of the relationships dont have blanks either. 

I even added the "remove empty" on the columns, even though there were no blanks. Same error. 

 

Could it be some sort of location/locale bug in power bi on web? (norwegian)

 

 

Highlighted
Frequent Visitor

Re: Error on Scheduled Refresh: Table contains blank values

I had the same issue I did process clear then recalc then  full 
and it works fine for me
because meta data has some old data so it does assume the empty value still there

 

Highlighted
Frequent Visitor

Re: Error on Scheduled Refresh: Table contains blank values

Hi, what do you mean by process clear?

Highlighted
Frequent Visitor

Re: Error on Scheduled Refresh: Table contains blank values

When you connect to SQL server management studio analysis services instance , right click and process your data base , you have some options of type of process you want to apply
There you will find process clear
Remember I fixed my data import and made sure no blank values imported
Then I did apply process clear and recalculate
Try it and let me know

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors