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.

[curth]

Yes, the reason I mentioned reverting the change is because there was a change from May to June where we added an optimization that simplifies expressions based on known type information. When the type information is wrong, the subsequent behavior might be wrong too.

[Anonymous]

Thanks for finding this, does this mean that the optimization could also make a mistake when the majority of a column is whole numbers and decimals exist 15000 rows down?

[Sergiy]

@curth , thank you for attention you paid.

I'd like to add a bit more to my feedback to June update.

I've made one more sample to add more details.

Link to the Power BI file:
https://www.dropbox.com/s/mklnk5fil5mbnad/JuneUpdate.pbix?dl=0

Link to the data source Excel file:
https://www.dropbox.com/s/ee8kns3roi0szwe/Book1.xlsx?dl=0

Query - Table1.
I connected to Excel file using Power Query Editor wizard.
Notwithstanding the data I connected to holds nullable values Power Query Editor generated code as if data was non-nullable:
"type text", "type date", "type datetime"
Your advice for me was to use "type nullable text" instead of "type text". So it would be logical if Power Query Editor wizard followed your advice as well generating "type nullable text", "type nullable date", "type nullable datetime".

(The following pictures are connected. They have my comments.)

1

2

3

4

5

[jimbucks]

We are experiencing similar issues with null values.  Not sure if this is all related to one fundamental issue with nulls or if this is a separate issue but this is the behavior we are seeing:

1. When adding a new conditional column based on two other columns with data type Decimal, Power Query correctly creates the new column with data type Any:
   
   
2. When changing the data type of the new conditional column from Any to Decimal, the system replaced the previously computed value with null:
   
   
3. If we omit the data type change in Power Query and import the results, the new conditional column is imported with data type Text and contains no value:
   
   

This is a pretty impactful issue and the only workaround I can think of until a solution is provided is to revert back to the May version of Power BI Desktop and manually refresh and republish reports and datasets.  Any idea when we might be able to expect a fix?

Thanks,

Jim

[curth]

Problematic scenarios involving something other than null are much less likely -- and are actually possible even in older versions. For instance, consider this query:

let
    Source = #table(type table [A=Int32.Type, B=Int32.Type], {{1, 2}, {3, 4.5}})
in
    Source

Here, we assert that the column types are whole numbers but one of the values is actually a decimal. If you load this this query into the data model, you'll see that the 4.5 has been replaced by a null value. That's because the code which loads the data into the data model believes that the types are correct even if they're not. (We do automatically make all types nullable at that point, but that's because we represent error values as null and we don't know in advance that a column might contain an error value.)

If, by contrast, you use Table.TransformColumnTypes to convert the values, i.e.

let
    Source = #table(type table [A=any, B=any], {{1, 2}, {3, 4.5}}),
    WithTypes = Table.TransformColumnTypes(Source, {{"A", Int32.Type}, {"B", Int32.Type}})
in
    WithTypes

then the conversion will round the 4.5 and you'll get a valid whole number (but not the actual exact value of 4.5).

Where the June change might come into play is if your predicate had an explicit type check like "each [Column1] is text" but that couldn't affect the difference between a whole number and a decimal number because these are both facets of the number type and you can't say "each [Column1] is Int32.Type".

[curth]

@Sergiy, yes the editor's behavior is wrong and we've filed a bug for it. But the wrong behavior in the editor has not previously caused a problem and we can't just start breaking user queries because of it. That's why we're going to rollback the change.

@jimbucks, without seeing the rest of the query text I find it impossible to speculate about what's happening. That said, columns in PQ with a type of "any" always get converted to text when importing into the data model because AS doesn't support column types of "any".

[jimbucks]

@curth, I can PM you query if you'd like.  It is pretty basic.  It appends a few other queries together and then does a Group By to create the Current Year and Projection columns (type number).  Afterwards, it's just this:

    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Actual / Projection", each if [Current Year] <> null then [Current Year] else [Projection]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Actual / Projection", type number}}),

For what it's worth, this is a production report and was working fine prior to the update. 

Thanks,

Jim

[curth]

@jimbucks, that doesn't tell me the type of [Current Year], which is almost certainly being made "type number" by the Table.Group instead of "type nullable number".

[jimbucks]

@curth, you are correct; Table.Group is creating Current Year and Projection as type number.  Editing the M code and changing Group By to use "type nullable number" instead of "type number" does resolve the issue.  Was this necessary in previous versions of Power BI Desktop or is this something new?  I do not remember encountering this issue prior to the June release.  

[Sergiy]

@curth ,

>That's why we're going to rollback the change.

Thanks for clarifying!

I'd greatly appreciate if you could add a bit more on the subject:

- Could you give an account of when it's going to be rolled back? (in a day/in a week/in two weeks time). 

- If we decide we can't wait and I'll be forced to mend broken reports by changing column types to "type nullable date" / "type nullable text", etc. , what happens to these changes when you've rolled back June update? Will that rollback affect reports again?

We're desparate at what to do: wait for a rollback or react right now by modifying queries' code(it'll take ~2days).
Could you please guide me giving a piece of advice?