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))
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))
- Goodkat11 months ago
Helper II
Hi dufoq3,
thank you for your answer! It is good to see that coalesce also works in the List.PositionOf! But as it has come up in this post, you are correct, using the condition directly as inputparameter in Table.Skip is smarter! I just did not know that this is even possible! You and other PQ enthusiasts have just shed a bright light here...
Thank you a lot!
Best regards, Andreas