Forum Discussion
List.PositionOf of first not null value in a column
- 11 months ago
Maybe something like this would work?
For the list...= {null, "", " ", 456, " ", "Abc"}= List.PositionOfAny(List.Transform(Source, each Text.Start(Text.From(_), 1)), {"0".."9", "A".."Z", "a".."z"}, Occurrence.First)Returns position 3 as the result.
- 11 months ago
Hi Goodkat,
Alternatively you can leverage the optional 4th parameter and provide a comparer function, for example.let Source = Table.FromValue({" ", null, "", "#(tab)", "FirstValue i=4"}), RowsToSkip = List.PositionOf( Table.Column(Source, "Value"), true, Occurrence.First, (x as any, y as logical) as logical => let s = try Text.Trim(Text.Clean(Text.From(x))) otherwise "" in (x <> null) and (s <> "") = y ) in RowsToSkip - 11 months ago
Hi Goodkat, if you wish to use your own solution, I've just updated your Position step:
= List.PositionOf(Quelle[Column1], "group", Occurrence.First, (x,y) => Text.StartsWith(x ?? "",y, Comparer.OrdinalIgnoreCase))...but, if you want to skip rows until "Group" appears I would prefer this:
= Table.Skip(Quelle, each not Text.Contains([Column1] ?? "", "group", Comparer.OrdinalIgnoreCase))
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)
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