Forum Discussion

Goodkat's avatar
Goodkat
Icon for Helper II rankHelper II
11 months ago
Solved

List.PositionOf of first not null value in a column

Dear Data Enthusiasts, Recently I learned about the 'Occurrence.First' parameter in 'List.PositionOf' and I was able to create a code line that saves me the need to add a index column and a filter...
Need Help
p45cal's avatar
p45cal
Icon for Solution Supplier rankSolution Supplier
11 months ago

Yes, eg.:

for PD:

= Table.Skip(Quelle,each Text.Length(Text.Select([Column1],{"a".."z","A".."Z","0".."9"}))=0 or [Column1]=null)

edit: prompted by dufoq3 's use of ?? (coalesce) we can lose the or [Column1]=null by:

= Table.Skip(Quelle,each Text.Length(Text.Select([Column1]?? "",{"a".."z","A".."Z","0".."9"}))=0)

 

For AP (keeping the headers row for promotion):

= Table.Skip(Quelle,List.PositionOf(Quelle[Column1],"#",Occurrence.First,each Text.StartsWith(_,"#"))-1)

Goodkat's avatar
Goodkat
Icon for Helper II rankHelper II
10 months ago

Dear AlienSx and p45cal,

 

Thank you both for your replies. Until now I thought the 'Table.Skip' does only accept numbers as input parameter... It now is really fascinating to see that MS did think about a much smarter way of allowing also conditions as input!

I already thought developing the number of rows by an index & filter solution is elegant, but no... Smarter is to use it all directly in Table.Skip.

 

Thank you both for that tremendous insight!!!

 

Best regards, Andreas