cancel
Showing results for 
Search instead for 
Did you mean: 

Web service refresh broke 25 hours ago - Column ... contains blank values

Web service scheduled refreshes began failing 25 hours ago with:

 

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

 

The table referenced has simple one-to-many relationships with 2 tables. The column referenced is the only column in that table - the 1 side of each relationship.

 

The published dataset and source (Excel files in SharePoint) have not been touched in over a week, during which time multiple web service scheduled refreshes have run without error.  A full refresh of the same PBIX file in Power BI Desktop does not reproduce this error.

 

The source query contained filtering to clean, trim and remove empty (to avoid this error), so I'm baffled.  I've tried everything I can think of - even replacing null with "Unspecified" on both sides of the relationships - still get the same error.

Status: Investigating
Comments
Frequent Visitor

I have had the same issue since yesterday, and I think I found the root in my case: The <> null check works fine on the text column as it comes raw from Excel, however it fails if it's applied after Text.Proper().

 

In one of the examples above I see the previous step is a Trim one. I suspect Text functions called on null values return a value that's not ~quite null (strangely enough, my PBI desktop is a couple months outdated, and the problem doesn't manifest there, only after I upload to services).

 

In any case, for my purposes, the null check was to create a custom column, and I need the custom column to be created after the Text.Proper(), so I ended up making a temp column  to store the null check results, doing the proper case conversion, and then making the custom column based on the check result and case-converted columns (rather than performing the check on-the-fly).

Power BI Team

It's not the Text.Proper itself which causes the problem but the fact that the code generated by the UI ascribes "type text" to the resulting column instead of "type nullable text". You could also work around the issue by editing the Table.TransformColumns steps to change the type value from "type text" to "type nullable text".

Power Participant

@curth  - I dont think the UI could be involved in my issue - the PBIX was sitting untouched in the web service when the first symptom appeared. With the June update to Power BI Desktop, I can get the error by just opening my file and pressing Refresh - not even opening the Power Query window.

Power Participant

There's an updated build of Power BI Desktop out (2.70.5494.761, dated 18 June 2019), and when I use that to refresh my unchanged file, the normal behaviour is now working again.  Null values are being filtered out.

 

The web service refresh on the same file is now working also, so it seems the same fix has been applied there also.

 

Please dont take my word for it - retest your files and post your results here.