Column contains blank values and this is not allowed

[sguenther]

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.

[hooodoo]

Having basically the same problem, but it also persists on Desktop refresh. I can confirm that there are NO blank or null values in the column that the error refers to.

[SonyaTVX]

Has this been fixed for anyone?

I am facing same issue were in there are no blank values in the referred column and have deleted the auto detected relationship as well but no luck. 

[mattcarter865]

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]

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]

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]

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

Thanks ;o)

[marcoselias]

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]

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

[Anonymous]

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]

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

[v-qiuyu-msft]

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

[sguenther]

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]

@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.

[sguenther]

@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.

[sguenther]

@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.

[v-qiuyu-msft]

Hi @sguenther,

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

Best Regards,
Qiuyun Yu