Forum Discussion
Goodkat
Helper II
1 year 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...
- 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))
m_dekorte
Resident Rockstar
1 year agoHi 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