(get list with) count of non-null values of each of multiple columns

[ooptennoort]

I have a data set with >100 cols, most of which contain only 1 value (useless). I need to remove these so I have cols left with >1 value.

So it seems to me I somehow need to List.Select all the List.NonNullCount >1 for each _column (which suggests using Table.ColumnNames) but I just can't figure out how to put these together followed of course by Table.SelectColumns with that list (the easy bit)

Any ideas (chatGPT provided absolute crap)?

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    cols = List.Accumulate(tcn, {},(s,c)=> if List.Count(List.RemoveNulls(Table.Column(Source,c))) > 1 then s & {c} else s), 
    Result = Table.SelectColumns(Source, cols)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Table1
2	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12	Col13	Col14	Col15
3	13	10	17	1						R	G	I	1
4	5	15	11		2					L	A	H
5	1	9	18			3				F	D	Q		2
6	7	12	3				4			O	C	B
7	6	4	2					5		E	P	M			3
8	16	14	8						6	K	J	N
9
10	Query Output
11	Col1	Col2	Col3	Col10	Col11	Col12
12	13	10	17	R	G	I
13	5	15	11	L	A	H
14	1	9	18	F	D	Q
15	7	12	3	O	C	B
16	6	4	2	E	P	M
17	16	14	8	K	J	N
18
Sheet1

 

[smozgur]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    cols = List.Accumulate(tcn, {},(s,c)=> if List.Count(List.RemoveNulls(Table.Column(Source,c))) > 1 then s & {c} else s),
    Result = Table.SelectColumns(Source, cols)
in
    Result

@JGordon11: Nice!

Just to mention another function name by using your solution, an alternative by using List.NonNullCount() instead of List.Count() and List.RemoveNulls() combination.

cols = List.Accumulate(tcn, {},(s,c)=> if List.NonNullCount(Table.Column(Source,c)) > 1 then s & {c} else s),

 

[ooptennoort]

@JGordon11: Nice!

Just to mention another function name by using your solution, an alternative by using List.NonNullCount() instead of List.Count() and List.RemoveNulls() combination.

cols = List.Accumulate(tcn, {},(s,c)=> if List.NonNullCount(Table.Column(Source,c)) > 1 then s & {c} else s),

Beautiful! Thanks for improving the solution!

 

[ooptennoort]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    cols = List.Accumulate(tcn, {},(s,c)=> if List.Count(List.RemoveNulls(Table.Column(Source,c))) > 1 then s & {c} else s),
    Result = Table.SelectColumns(Source, cols)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Table1
2	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12	Col13	Col14	Col15
3	13	10	17	1						R	G	I	1
4	5	15	11		2					L	A	H
5	1	9	18			3				F	D	Q		2
6	7	12	3				4			O	C	B
7	6	4	2					5		E	P	M			3
8	16	14	8						6	K	J	N
9
10	Query Output
11	Col1	Col2	Col3	Col10	Col11	Col12
12	13	10	17	R	G	I
13	5	15	11	L	A	H
14	1	9	18	F	D	Q
15	7	12	3	O	C	B
16	6	4	2	E	P	M
17	16	14	8	K	J	N
18
Sheet1

Thank you!!! Of course, iteration... that scary list.accumulate... one day I will be able to (re)produce it! Thanks again!!