- 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.
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.
Frequently Asked Questions
Sort ascending or descending on a column. Usually, the null value should sort on the top, and there you can uncheck the null mark. If you want to disable sorting in Power BI, use our easy guide.
Select Show items with no data option from a column. You will see the Product Subcategory with the blank values.
Select the cell value that you want to change in the Query Editor. Then select Replace Values option from the Home tab. Now you should be able to change null to Unspecified. If you’re struggling, read more information on Power BI.