cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BKnecht
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?

 

 

 

17 REPLIES 17
yji
New Member

Hi, if I use direct Oracle database connection to get table, my local laptop's PowerBi Desktop use SQL Server Analysis Services (see from Task Manager), local laptop don't have Analysis Service configured at before. I'm wondering where those matadata or table caches are located? Process Clear from where? local pc or remote database server? 

guisin
Frequent Visitor

I was able to resolve the issue by :

1. Removing whatever the new calculated column you've created

2.  Process All to refresh the Meta data of the DB

3.  Re-create with no blank value

 

So basically just a glitch, in my opinion

 

 

 

khaled_cs
Frequent Visitor

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

 

Hi, what do you mean by process clear?

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

Hi, I'm connecting to a MySQL server, installed on an AWS instance. Is what you said relevant to my case?

yes 
please check this doc https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/process-database-table-or-part...

this will be answer for the following case 

if you get this error message "Table contains blank values"
you cleared your database and make sure there is no empty/blank values in your database 

but everytime you try to run you get this issue 

the meta data of the DB need to be refreshed 

you can update the whole DB or just the table you are working on 

 

To process a database

  1. In SQL Server Management Studio, right-click on the database you want to process, and then click Process Database.

  2. In the Process Database dialog box, in the Mode listbox, select one of the following process modes:

    Mode Description
    Process DefaultDetects the process state of database objects, and performs processing necessary to deliver unprocessed or partially processed objects to a fully processed state. Data for empty tables and partitions is loaded; hierarchies, calculated columns, and relationships are built or rebuilt (recalculated).
    Process FullProcesses a database and all the objects that it contains. When Process Full is run for an object that has already been processed, Analysis Services drops all data in the object, and then processes the object. This kind of processing is required when a structural change has been made to an object. This option requires the most resources.
    Process ClearRemoves all data from database objects.
    Process RecalcUpdates and recalculates hierarchies, relationships, and calculated columns.
  3. In the Process checkbox column, select the partitions you want to process with the selected mode, and then click Ok.

elxaxe
Frequent Visitor

Hi,

 

Has anyone solved this?

 

I'm facing almost the same issue. Error message is: Column '' in Table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

 

But this is happening today as yestearday no issue happened. The calculated column that gives this error is calculated like this:

GOAL = LOOKUPVALUE(Services[GOAL]; Services[Case_ID]; Orders[Case_ID]) 

An GOAL needs to have null values (because newest services aren't estimated yet) and these null values are checked later.

 

khaled_cs
Frequent Visitor

If you process full should work fine , otherwise you always will need to create a new column
elxaxe
Frequent Visitor

Well, it seemed to be an error when changing the data type ine the source table.

Just created a new calculated column with the same calculation, deleted the old one and renamed the new as the old. Cat Indifferent

vprady
Advocate I
Advocate I

 

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

I was getting the same error message - this solution worked for me.

 

Thank you!

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)

 

 

alexbar
New Member

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.

amitbansal
New Member

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,

Greg_Deckler
Super User IV
Super User IV

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.


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

@ me in replies or I'll lose your thread!!!

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!




cwayne758
Helper IV
Helper IV

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!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors