Select rows if …

[adambc]

I’ve got a PQ that’s working perfectly until the last step!

I have 3 columns I want to filter in as follows …

In main PQ, select rows if ALL 3 columns contain a value ie NONE of the columns are null …

In a duplicate of the main PQ, select rows if ANY column is null ie 1 or many of the columns ARE null

Can anyone help me with the M code?

Thanks …

 

[GraH]

col1	col2	col3
a	b
d	f	e
4	5	7
	9
x	y	z

let
    Source = Table.ToRows(sample),
    NotNull = List.Select(Source, each List.NonNullCount(_)=3),
    RestoreTable = Table.FromColumns(NotNull)
in
    RestoreTable

let
Source = Table.ToRows(sample),
NotNull = List.Select(Source, each List.NonNullCount(_)<3),
RestoreTable = Table.FromColumns(NotNull)
in
RestoreTable

 

[adambc]

col1	col2	col3
a	b
d	f	e
4	5	7
	9
x	y	z

let
    Source = Table.ToRows(sample),
    NotNull = List.Select(Source, each List.NonNullCount(_)=3),
    RestoreTable = Table.FromColumns(NotNull)
in
    RestoreTable

let
Source = Table.ToRows(sample),
NotNull = List.Select(Source, each List.NonNullCount(_)<3),
RestoreTable = Table.FromColumns(NotNull)
in
RestoreTable

@ GraH

Not sure what this is doing, or how to use it with my table?

Have I confused by saying “I have 3 columns I want to filter”?

I have other columns too - and my 3 columns are not all adjacent (2 are, but the 3rd isn’t).

 

[GraH]

@adambc , maybe you can enlighten us by providing a sample of your data, be it preferably anonimized.