Forum Discussion

Goodkat's avatar
Goodkat
Icon for Helper II rankHelper II
11 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...
Need Help
  • jgeddes's avatar
    jgeddes
    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.

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

     

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

     

tayloramy's avatar
tayloramy
Icon for Super User rankSuper User
11 months ago

Hi Goodkat,

 

You’re on the right track. The hiccup comes from List.PositionOf looking for an exact value, not a predicate-so if you pass it a value that sometimes evaluates to null/""/blanks, it can’t reliably find the first “proper” cell. The clean pattern is: map the column to a Boolean list (true = “proper”), then find the first true.

Below are copy-pasteable options that avoid adding an index or filtering steps.

 

let
    // Replace with your table and column
    Source   = #"Your Previous Step",
    Col      = Table.Column(Source, "YourColumnName"),

    // Proper = not null and, when trimmed, not empty
    IsProper = (v as any) as logical =>
        v <> null and
        let s = try Text.Trim(Text.From(v)) otherwise ""
        in s <> "",

    // Map to booleans, then find first TRUE
    Pos = List.PositionOf(
            List.Transform(Col, each IsProper(_)),
            true,
            Occurrence.First
          ),

    // Example: how many rows to skip (0-based index)
    RowsToSkip = if Pos >= 0 then Pos else null
in
    RowsToSkip
  • Pos is the 0-based position of the first non-null, non-blank, non-empty-after-trim entry.
  • If nothing qualifies, you’ll get -1; I turned that into null in RowsToSkip for safety.

 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

  • Goodkat's avatar
    Goodkat
    Icon for Helper II rankHelper II
    11 months ago

    Hi Tayloramy,

     

    thank you for your reply! It is an interesting solution to create a list of clean booleans. So far I have not often seen a solution with a tailormade function 'IsProper' to be used in a following step, but I get an idea of its power. I will further test and comment the logic for me in my test environment!

    Thank you for sharing this approach!

    Have a good weekend!

     

    Best regards, Andreas