Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
5 years ago

Filter columns if all rows contain data

I am creating a report to show all records that have missing data, so that the dataset owner can update the missing data.   I currently have a table that shows rows that have missing data and as a ...
Need Help
AlB's avatar
AlB
Community Champion
5 years ago

Hi StuartSmith 

You can do this best in Power Query. Place the following M code in a blank query to see the steps of an example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMgTjWJ1oJSMgC4iMwBxjIAuEwRwTIAOCY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "isComplete", each not List.Contains(Record.ToList(_), null)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([isComplete] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"isComplete"})
in
    #"Removed Columns"

 The code add a custom column that check if any of the columns if that row is empty (null). It then filters out the rows that are complete. Note that you might have to tweak the code a bit if instead of null you are looking for "" for text columns

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers