cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Adding custom column causes "blank values error"

Hello,

 

I have a text column which contains in some rows only numeric entries and in other rows also text. I want to add now another column which takes the numeric values as they are and replaces all the text values by zeros. I do that like that:

 

 

= Table.AddColumn(#"Step before", "Column2", each try Number.From([Column1]) otherwise 0)

 

 

In the query editor this works perfectly. However as soon as I want to apply changes  I get the infamous  "Column 'Column3' in Table 'mytable' 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."-error.

 

This happens also if I break all relationships to this table. Note also that Column3 is mentioned in the error which is independent of Column1 and Column2 that are used for adding my custom column. Manually removing empty rows and nulls after adding my column also doesn't fix the problem.

 

Does anyone have a clue what I am doing wrong?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

Finally I found the problem myself: the column that was mentioned in the error message was also a custom added column and I had added it with "Int64.type". For whatever reason this didn't cause any issues until I changed the type of the other column. Removing the "Int64.Type" from the first column, fixed the issue - although I don't have the slightest idea why because these two columns don't have anything to do with each other.

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Yeah, that doesn't make any sense if you do not have any relationships to that table.

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Ok, I have to correct myself: Adding a column doesn't seem to be the source of the problem. If I manually filter out all the Text values in the column and then use 

= Table.TransformColumnTypes(#"Filtered Rows",{{"Column", type number}})

to create a numeric column it looks fine in the query editor but as soon as I apply the changes the error still appears. 

Frequent Visitor

Finally I found the problem myself: the column that was mentioned in the error message was also a custom added column and I had added it with "Int64.type". For whatever reason this didn't cause any issues until I changed the type of the other column. Removing the "Int64.Type" from the first column, fixed the issue - although I don't have the slightest idea why because these two columns don't have anything to do with each other.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors