Forum Discussion

Goodkat's avatar
Goodkat
Icon for Helper II rankHelper II
10 months ago
Solved

List.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...
  • jgeddes's avatar
    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.

  • m_dekorte's avatar
    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

     

  • dufoq3's avatar
    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))