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?
Ran into this issue several times recently. Finally found the cure. Posting here for future reference.
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.
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.
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.
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
Parsing of Text - this is the column that becomes blank on PBI Service.
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.
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.
@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.
I'm trying to involve senior engineer to take a look at this issue. You patience is greatly appreciated.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!