Removing Data

[RobinHaerkens]

Hi all,

Quick question regarding removing data - from the below data, I would like to delete all rows that contain "null" in one of the columns. Could anyone help on this?

Thanks a lot,

Robin.

[AlienSx · Accepted solution]

hello, @RobinHaerkens 

Table.FromRows(
  List.Select(
    Table.ToRows(your_table), (x) => not List.Contains(x, null)
  )
)

[RobinHaerkens]

Hi @AlienSx 

I'm getting the below error when implementing your code - can you help?

[AlienSx]

Hi, @RobinHaerkens please show your code. I've just tested it and it works. 

let
    source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBIdeK5IzEvPRUhaLEklQriJhSrA5MmrCAU2p6Zl5eZl66QlJiTmJecipQLCC1KDM/RSExuSSzLLOkEmRNXgqyEpD2vHyFvNKcnGKgLPHM2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    your_table_with_nulls = Table.ReplaceValue(source,"",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    tbl = 
        Table.FromRows(
            List.Select(
                Table.ToRows(your_table_with_nulls), (x) => not List.Contains(x, null)
            )
        )
in
    tbl