Service Report Differs from Desktop app - null rows

[edhans]

I know there was an issue in June with the NULL field and not being calculated properly, but it seems to still exist, at least in the service. 

I have the following M code in a table:

let
    Source = ARALTADR,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Customer Number] = "BED100" or [Customer Number] = "BED200")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Customer Number", "Alternate Address", "Pool Facility Codes"}),
    #"Filtered out nulls" = Table.SelectRows(#"Removed Other Columns", each ([Pool Facility Codes] <> null)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered out nulls", {"Pool Facility Codes"}, tblBBBPoolFacilityCodes, {"FacilityCode"}, "tblBBBPoolFacilityCodes", JoinKind.LeftAnti),
    #"Expanded tblBBBPoolFacilityCodes" = Table.ExpandTableColumn(#"Merged Queries", "tblBBBPoolFacilityCodes", {"FacilityCode"}, {"FacilityCode"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded tblBBBPoolFacilityCodes",{"Customer Number", "Alternate Address", "Pool Facility Codes"})
in
    #"Removed Other Columns1"

The third row is removing nulls from a text field. In the desktop app (June 2019) it works fine, but when I publish to the service, that filter is being ignored and is returning all records.

They clearly show as null in the Power Query editor on the desktop. 

I changed my query to this, which moves the null filtering after the join (less efficient, but wanted to test it)

let
    Source = ARALTADR,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Customer Number] = "BED100" or [Customer Number] = "BED200")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Customer Number", "Alternate Address", "Pool Facility Codes"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"Pool Facility Codes"}, tblBBBPoolFacilityCodes, {"FacilityCode"}, "tblBBBPoolFacilityCodes", JoinKind.LeftOuter),
    #"Expanded tblBBBPoolFacilityCodes" = Table.ExpandTableColumn(#"Merged Queries", "tblBBBPoolFacilityCodes", {"FacilityCode"}, {"FacilityCode"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded tblBBBPoolFacilityCodes", each ([Pool Facility Codes] <> null) and ([FacilityCode] = null)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Customer Number", "Alternate Address", "Pool Facility Codes"})
in
    #"Removed Other Columns1"

And the service still ignores the <> null filter.

I finally applied the text type again to the Pool Facility Code field above and that caused it to work correctly in the service. I thought this was all resolved last month?

[v-shex-msft]

HI @edhans ,

Can you please share a sample file for test? I can't reproduce this on my side.
In addition, have you try to filter with other keywords?(e.g. " ", "")
Regards,

Xiaoxin Sheng

[edhans]

It goes to my SQL server, so not sure how I can provide a set of sample data. I would be glad to PM you the file if desired, but without the backend database, not sure how that would work.

I am 100% sure it isn't a "" or " " issue, because by simply setting the type to text (it is already text) fixes it, which is reminiscent of the other null bug that we had last month that is you reapplied the same data type to the field, subsequent handling of NULL worked correctly.

[v-shex-msft]

Hi @edhans ,

Maybe you can open a support ticket to report and get better support for this:

submit a support ticket

Regards,
Xiaoxin Sheng