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

[mike_honey]

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.

[Anonymous]

We are experiencing an issue with these exact symptoms in our organization (meaning, untouched reports now erroring w/ "Column ... contains blank values on one side", frequent automated refreshes worked prior to this, and the source queries explicitly remove data with blanks where pBI is reporting issues).

Notably, these same reports work in Desktop, but fail to refresh once published to the server.

We have been able to re-create some (simple) reports which have started experiencing this issue and see them working, but have some more complex reports/models which re-working would be very time intensive.

We did find this thread: https://community.powerbi.com/t5/Desktop/Error-on-Scheduled-Refresh-Table-contains-blank-values/td-p... which may be describing a similar issue but are unsure.

[mike_honey]

Thanks for confirming @Anonymous .  Please also vote to try to raise attention for this issue.

I've tried updating Power BI Desktop to the June release that just landed.  Now I'm getting a consistent error message there, which also doesnt make any sense. 

Power Query preview now shows the null row, even immediately after a Filter [Column] <> null.  So it seems the Filter functionality is broken?

Tried Replace Values on null - it has no effect.  Finally came up with a hackaround - add a column for the Length of the key column, then Filter for Length > 0.

Tried to repro by making a new file and using Enter Data, but the Filter step works as expected.  I'm totally confused, and fearful ...

[v-yuta-msft]

@mike_honey ,

I have made a test but I couldn't reproduce your issue. This error would also occur on desktop side if there's blank value in primary key column of one-side table as below. Please update power bi desktop to the latest version.

 

In addtion, to replace the blank will null or NULL, this solution can work on my side.

Regards,

Jimmy Tao

[mike_honey]

Hi Jimmy,

Thanks for looking into this so quickly.

As noted above I have updated Power BI Desktop to the June 2019 update- now it shows the same incorrect error as the web service.

I understand the reason for the error, but still consider it is incorrect.  The Query Steps include Remove Empty it should not be possible to delivery null/blank data into the model. It certainly wasnt getting through prior to 48 hours ago and the June 2019 update.

The root cause appears to be in the Query Steps, where Filter to Remove Empty is no longer working, e.g.

This should be impossible, right? I've never seen this behaviour in the last 5+ years working with Power Query, and it wasnt behaving like that until the very recent update.

[JensB]

@v-yuta-msft The root cause here seems to be that in PowerQuery evaluating "if [ColumnName] <> null" goes wrong, i.e. returns "true" even if the column actually is "null".

I can reproduce that in PowerBi Desktop version: 2.70.5494.562 64-bit (June 2019)

The report still worked fine as long as I had the May version.

Best regards

Jens

[mike_honey]

Thanks @JensB for confirming - I thought I was going crazy when I first saw it.

As you have mentioned, the impact is potentially a lot wider than my initial symptom - could also affect calculated columns etc. 

[mike_honey]

This issue has further confirmation, with many other users reporting the same root cause issue (Filter <> null does not work) on this thread:

https://community.powerbi.com/t5/Issues/June-2019-Update-Cannot-Filter-Null-Dates/idc-p/712049#M4473...

[curth]

Is anyone seeing this with a data source other than "Enter Data"?

[mike_honey]

@curth  My data sources are Excel via SharePoint. 

I tried to repro it using Enter Data, but the Filter <> null seems to work ok for that scenario.