Forum Discussion
Goodkat
Helper II
10 months agoList.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...
- 10 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.
- 10 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 - 10 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))
jgeddes
Super User
10 months agoMaybe 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.
- Goodkat10 months ago
Helper II
Dear Jgeddes,
Wow! It works so nicely and with such concise statement. I am honest, I had to read a bit more on List.Transform to fully understand its impact here. Now it is clear & I have learned.
Thank you so much for sharing your knowledge with me!
Have a good weekend & best regards, Andreas