Forum Discussion
List.PositionOf of first not null value in a column
- 1 year 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.
- 1 year 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 - 1 year 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,
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
Hi m_dekorte,
I am truly delighted! It is Friday afternoon and I have the honour to read six (!) replys to my post. And every solution is a bit different and provides another learning for me!
In your case I now realize that the '4th parameter' is NOT limited to the standard of 'Comparer. x y z', but is open to another 'let' statement... And also you are searching for logical of 'true'! I thought search is limited to character & numbers... I am a profound user of PQ, but to me this is an mindblowing insight!
Thank you so much!!!
Best regards, Andreas