- Power BI Service and the Desktop client allows the users to have a dashboard scheduled to refresh daily using the table.
- If you happen to have any blank rows in the table, you may see Column contains blank values and this is not allowed many-to-one relationship.
- If you need help with Power BI, we have a PowerBi Tutorials And Guides section just for that.
- For news and support concerning Power BI, visit our Power BI Hub.
Power BI Service and the Desktop client allows the users to have a dashboard scheduled to refresh daily using the table. If you happen to have any blank rows in the table, you may see Column contains blank values and this is not allowed many-to-one relationship.
Several Power BI users have reported a similar error in the Power BI Community forum.
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.
Follow the steps listed in this article to troubleshoot this error in Power BI service and desktop client.
How can I fix the power BI error blank values?
1. Delete Invalid Relationship Between Data Sets
- At times Power BI may automatically create relationships between the queries that are being used to drive the data in the reports. Here is how to fix this.
- Launch Power BI Service.
- Go into Manage Relationships.
- Make sure that there is a relationship listed.
- Now check all the Form and To relationship that is available in the Active section.
- You need to delete any invalid From and To relationships between separate data sets.
- If you have two very different queries pulling data from separate source identical columns names then Power BI will generate a relationship between them which would be invalid. The error should resolve after you have deleted the invalid relationship.
2. Disable Include Relationship Columns Option
- In the window where you enter the server name, click on Advanced Options.
- Now uncheck Include Relationships columns option.
- Now check if the error occurs again as it should stop Power BI from identifying related columns resulting in the error.
3. Other Solutions to Try
- Try to clean the trailing and leading spaces in your data in PQ Transformation.
- On Power BI Desktop, go to File > Options. Click on Settings and Options. Now uncheck all the options under Relationships.
- Try to edit the load query and replace the blanks with some useful info such as document creation date or time etc.
- Go to the table that is referenced in the error message and try to sort your column in ascending/descending order.
- Remove and recreate the query. As a last resort, you can try to create and build a new query after deleting the older one.
FAQ: Read more about blank and null values in Power BI
- How do you remove null in power bi?
- How do you show blank values in power bi?
- How do I change blank null value in power bi?
Editor’s Note: This post was originally published in August 2019 and has been since revamped and updated in May 2020 for freshness, accuracy, and comprehensiveness.