June 2019 Update - Cannot Filter Null Dates

[Anonymous]

Prior to the June 2019 update, I was able to filter out null date values using the filter. However, that is no longer working. Has anyone else encountered this? Null is a valid value for this date column.

I have tested this in Power BI without the June 2019 update and it works as expected - that is, I can replace the null value or filter the table.

The below pictures show the columns. As shows, the null value is present. 

Even if I try to do a replace, it doesn't replace the value = Table.ReplaceValue(#"Added Conditional Column",null,#date(1999, 1, 1),Replacer.ReplaceValue,{"resolution_dt"})) 

I have confirmed that in the June update that you can still filter out the null values on a text field.

Is there a link to download the May 2019 version so I can keep developing until this is fixed? I have confirmed in another PBIX on a machine which did not update that this functionality works.

[Sergiy]

Confirm 😞

Damage done by June 2019 update is nearly fatal.

The workaround is to change column types: Text -> Text, Date -> Date, etc

Picture where the bug isn't fixed:

Picture where the bug is fixed:

The sample file demonstrating this bug could be downloaded using the following link:

https://www.dropbox.com/s/dje9zllkra1jdyw/NullValueFilter.pbix?dl=0

@v-qiuyu-msft, what's the point in using May version of Power BI Desktop? Half of my reports that are published to Power BI Service couldn't be updated now, the other half show inconsistent data.

Unbelievable carelessness of Microsoft 😞

[MBF]

@v-qiuyu-msft 

Your sample has the same behaviour we're all reporting.

Specifically, the "Change Type" is causing the issue to be masked. Change your sample to use the following code, and you'll see the exact affect we're all describing.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NtA3MjCwUDA0sjIwACIFR18lHaW80pwcGBWrQ7k6U6AgEBlakmCoKUQaqIcaLiBLXSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SellStartDate = _t, SellEndDate = _t, DiscontinuedDate = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,null,"0",Replacer.ReplaceValue,{"SellEndDate"})
in
    #"Replaced Value"

Expected Behaviour:

- All null values replaced by 0

Actual Behaviour

- All null values remain nulls.

[curth]

Is there anyone seeing this as an issue with a data source other than "Enter Data"?

[curth]

Okay, the closest example above works as expected because the table doesn't actually contain nulls; it only contains text values with the text "null".

The example before that is failing because the call to Table.TransformColumns is asserting that the new column has "type date" when in fact that's incorrect because the column actually has "type nullable date".

The behavioral change that's responsible for this is that we're currently taking the type into account when simplifying predicates, so if you say for a non-nullable column Column1 "each [Column1] = null" we simplify that to "each false". It looks like this is too aggressive based on what people are currently doing in their queries.

[MBF]

@curth 

If it helps, I did manage to get it to trigger in PostgreSQL - but not "directly". My previous image example shows this as well.

If I use a postgresql data source, and then "group" a text column, any aggregated text values that return null are now failing to treat the null properly.
e.g., List.Max([String field]).

Expected Behaviour:

- A null value in a string column that has List.Max applied, should treat the null as null after the List.Max is applied.

Actual Behaviour

- A null value in a string column that has List.Max applied, is not applying a type to the null value correctly. Hence all subsequent operations dealing with "null" or null are failing to apply.

// This does not work:
= Table.ReplaceValue(#"Grouped Rows","null","Other",Replacer.ReplaceValue,{"String Column"})
// Nor does this
= Table.ReplaceValue(#"Grouped Rows",null,"Other",Replacer.ReplaceValue,{"String Column"})

Important point

If the replacement of the null value is applied PRIOR to the "group" transformation, the replacement works correctly as expected. This indicates to me that List.Max is also changing the type of the null value, or getting confused recently about how it's meant to treat null values.

Unless List.Max is changing the type of the column it's performing on, I don't see how your example is applicable in this case. Isn't a null value still null before and after the group by?

i.e., this isn't limited to "Enter Data".

Example:

    Source = #"PostgreSQL Data",
    // Replacement of nulls in "String Column" works fine here.
    #"Grouped Rows" = Table.Group(Source, {"Unique ID"}, {"String Column", each List.Max([String Column]), type text}}),

    //  Replacement of nulls in "String Column" fails after Table.Group
    #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows","null","Other",Replacer.ReplaceValue,{"String Column"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"Other",Replacer.ReplaceValue,{"String Column"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"String Column", type text}})
    // Replacement of nulls in "String Column" works fine again after re-telling Power BI it's text....

[curth]

I see. The problem is that the UI has ascribed a type of "type text" to the result of the List.Max operation and it should actually ascribe a type of "type nullable text". 

We're clearly going to need to revert this change.

[mike_honey]

@curth I dont think it's just in the UI - also the refresh engine. Ref the other thread, I first encountered a symptom of this issue in a scheduled web service refresh, with file unchanged for over a week, before the June 2019 Desktop update was announced.

[Sergiy]

Dear @curth ,

>Is there anyone seeing this as an issue with a data source other than "Enter Data"?

I made another test environment so that you could see it for yourself. 

Power BI file:

https://www.dropbox.com/s/jluw0nkc0nouqo6/FilterTest_Excel.pbix?dl=0

Excel source file:

https://www.dropbox.com/s/ndo9h9sxrd6te99/Book1.xlsx?dl=0

[curth]

@Sergiy, you are adding a column and specifying its type as text even though it can return a null value. The type in the call to Table.AddColumn should be "type nullable text" instead of "type text".

(This is true even once we undo the change that caused the wrong type to have this effect.)

[Anonymous]

@curth 

I don't know that your assesment of @Sergiy  PBIX is correct.

I have done the same conditional in the May release and works fine without specifying nullable text. If we need to do that, then that's a large change for some of my queries.