Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jaryszek
Post Patron
Post Patron

Filter creates null values in Power Query in Excel

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

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @jaryszek ,

 

right.PNG

 

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.

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

Hi @jaryszek ,

 

right.PNG

 

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.

@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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors