Filter creates null values in Power Query in Excel

[jaryszek]

Hi Guys,

my DAX is :

let
    Source = Excel.CurrentWorkbook(){[Name="t_SourceVolumes"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"Topology", "Tier"}, t_VolumeCustomizationsSource, {"Topology", "Tier"}, "t_VolumeCustomizationsSource", JoinKind.RightOuter),
    #"Filtered Rows" = Table.SelectRows(#"Merged Queries", each ([Function] = "root") and ([TotalServers] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"TotalServers", "Ordinal", "OSType", "OSDistro", "OSVersion", "ScalingParadigm", "DeploymentName", "TopologyName", "TierName", "TierFunction", "VolumeDesignKey", "RowMatchVolumeDesign", "VolumeComponent", "ANFDisk", "VolumeProtocolKey", "RowMatchVolumeProtocol", "StorageProtocol", "ProtocolVersion", "VolumeMountOSOptionsKey", "RowMatchVolumeMountOSOptionsKey",  "DiskDriveType", "ReplicationScenario", "StorageType", "VolumeSize", "CustomizationKey", "VolumeDesignKeyDefault", "RowMatchVolumeDesignDefault", "PhysicalDiskModelDefault", "MountPathDefault", "NumberOfDisksDefault", "ProviderFSTypeDefault", "ConsumerFSTypeDefault", "VolumeSizeDefault", "DiskKeyDefault", "ScaleDefault", "DefaultKey", "IfCustomized", "t_VolumeCustomizationsSource"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NumberOfDisks (including DiskNumberCapacity)", "NumberOfDisks"}, {"PhysicalDiskModel", "DiskModel"}, {"StorageSelector", "StorageType"}, {"ProviderFSType", "ProviderFileSystemType"}, {"ConsumerFSType", "ConsumerFileSystemType"}, {"DiskSize", "VolumeSizeInGB"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each true),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [Environment] <> null),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each true),
    #"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows3", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

The problem is that in first "Filtered rows" line i am getting one additional row with null in each column. Why? 

What is happening. Merged tables are qithout any nulls and filter triggers it somehow...

Furthermore - when i am adding in next step one additional filter to filter out nulls - it is not working! Nulls are still there!

Can anybody help?

Jacek

[Icey · Accepted solution]

Hi @jaryszek ,

You have selected "Right Outer (all from second, matching from second)" when merging queries. "Right Outer" will keep all rows from the second table. When you filter columns from the first table, the records from the second table will be retained.

You can try to change the Join Type to "Left Outer" in your scenario.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@jaryszek ,
Just a follow up on the solution provided by Icey. It is very similar to what I suggested, but the solution from Icey may still result in null values in the 'right' table. If you don't want ANY null values you can choose INNER join, to only find rows that exist in both tables.

Careful with merges too, as you can add rows of duplicate values (not just NULL) to your data if you're not careful.

See if this post helps with merges: https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query/?ref=818

[AllisonKennedy]

Hi Jacek~

Are you able to share screenshots with sensitive info removed/covered?

For these screenshots can you please click on the view tab to turn on the column distribution and show that for both source tables (t_VolumeCustomizationsSource and t_SourceVolumes )

There is no reason I am aware of that a filter step would add rows, so is it possible the null row was just hiding? How many rows in your tables?

Finally, why have you chosen RIGHT OUTER join? If you would like to have no null values, then choose INNER join.