cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sguenther
Advocate II
Advocate II

Column contains blank values and this is not allowed

Hey,

 

I got a question regarding relationships. When updating my dataset in the service I often get the following error:

 

"Column 'Column1' in Table 'Table1' 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."

 

I understand what this means, but I don't know why it happens. First of all there are safety measures in our database to prevent this from happening and secondly in my queries I filter out rows with blank values for any columns used in a relationship.

 

#"Removed empty Project IDs" = Table.SelectRows(#"Removed Other Columns", each [Column1] <> null and [Column1] <> ""),

 

So this error should not happen for two reasons.

 

Any idea what I am missing that could fix this problem?

 

Thank you.

14 REPLIES 14
mattcarter865
Frequent Visitor

Ran into this issue several times recently. Finally found the cure. Posting here for future reference.

Chris Webb's BI Blog: The Is Nullable Column Property In Power BI Chris Webb's BI Blog (crossjoin.co...

jasminwing
Frequent Visitor

Hello

 

I am having the same issue. When building reports in the desktop version, I am unable to create a enw column as the error message is:

 

Column " in table" contains blank values and thsi 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.

 

I am trying to use the following formula to calculate the number of days and weeks between two dates. I have used this basic formula on many other tables successfully.

 

REMEDIAL WEEKS = Completions[COMPLETED_DATE_WEEKNO] - Completions[CREATED_DATE_WEEKNO]

REMEDIAL DAYS = Completions[COMPLETED_DATE] - Completions[CREATED_DATE]

 

There is currently only one relationship involving the 'completions' table. It is linking a field called 'order number' to the 'order number' in another table. I can only set this as a 'many to one' relationship and it is the only field that is present in both tables in order for me to match them.

 

Any help would be greatly apprecaited.

 

Thanks

v-sheset
Frequent Visitor

I am bringing my Data from Kusto and having the same proble. 

I infact deleted the relationships and have all reports build on flat files, so there is no point of relationships or Primary Key, However I am still seeing the error. Infact It keeps pointing to a column that has blank values, when that column does not exisit in the Query 😞

I am not sure how to fix this.

LisbethSchoman
Frequent Visitor

Hi have the exact same issue, and I'd like to be updated if anyone finds the answer to solve this.

Thanks ;o)

marcoselias
Advocate V
Advocate V

I am having the same issue. I'm trying to connect to an Oracle database. I tried Direct Query and Import but having the issue in both. The count of rows when the data is being pulled matches the number of rows in the table but nothing seems to get imported.

 

UPDATE: Here's something that may help - click on "Advanced options" in the window where you enter the server name and unselect the checkbox called "Include relationship columns" before importing data. Perhaps the error occurs because it identifies the column as a potential relationship column and it is blank or null in any of the related tables.

csabil
Advocate II
Advocate II

It happens to me as well, with a table I created fully manually with hard inputs.

Me too. I have columns that are fine on refresh from Desktop but becomes blank on refresh in the service. I've already tried rebuilding my query just now but I still got the same results.

 

My queries contain:

Merging of date and time for Unpivoting 

Extracted Date, Time and Hour

Conditional Columns

Parsing of Text - this is the column that becomes blank on PBI Service.

 

erantdo
Frequent Visitor

It's constantly happenning to me as well. Scheduled refresh is sooo buggy.

v-qiuyu-msft
Community Support
Community Support

Hi @sguenther,

 

In your scenario, if you have stored .PBIX file of this report, please open the report in Power BI desktop, then click Refresh button to check if the same issue occurs. Also please check if the column Column1 in "Table1" contain the blank values in Query Editor.

 

If the report runs fine in desktop, please re-publish the report to Power BI Service and refresh the dataset to see if issue persists.

 

Best Regards,

Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft,

 

Did all of the above already. Refresh works in the desktop application and it also doesn't show blank values in the query editor.

Problem persists after re-upload. That's why it is so strange.

 

Best regards,

Sebastian

Vicky_Song
Impactful Individual
Impactful Individual

@sguenther, are you able to create relationships successfully in the Power BI Desktop? If so, please delete datasets from the PBI online service and re-publish to see if it works.

@Vicky_Song creating successful relationships in PowerBI Desktop is possible. But deleting the dataset from the Service would also destroy all reports based on this dataset, which have been created in the service, correct? This is not a viable option as long as it's not possible to download or backup reports built in the service. And uploading the dataset under a different name did not help as far as I remember.

@Vicky_Song @v-qiuyu-msft This Problem persists until today and happened increasingly more often since the latest PowerBI service update. I have no idea how to fix it and our company reporting capabilities are severly limited because of this issue.

Hi @sguenther,

 

I'm trying to involve senior engineer to take a look at this issue. You patience is greatly appreciated.  Smiley Happy

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!