List.PositionOf of first not null value in a column

[Goodkat]

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 step. I want to identify the number of the datarecord, where the databody starts, to know how many rows to skip during import. The example is in query 'AP'.
In a second datasource I wanted to use similar approach, but failed. I want to find the datarecord with a first proper (not null, not blank, not "", OR Length Trim/ TrimStart > 0) entry. So I put a Text.Length & Text.Select combination into a List.First & List.Select. And I can extract the first value of a column. I also thought about using List.RemoveNulls, but there can be blanks and "" in the cells and there it would fail.
But from knowing what the first proper value is, I still do not have the position! So I put the result into a List.Position statement, like in the query 'AP', but apparently I fail with some null values above my data.
Moreover: Is there a smarter way to utilize the List.PositionOf & Occurrence.First combination to find the first proper value in a column? All without adding index column and filtering on column1 and reading the index.

https://c.gmx.net/@324888734501700174/Z5PHfTIKNEXSqg9FtNb5qw

I am looking very much forward some thoughts and directions.

Thank you!

Best regards, Andreas

[jgeddes · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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))

[Goodkat]

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

[AlienSx]

@Goodkat smarter way would be to utilize Table.Skip/Table.RemoveFirstN's 2nd agrument in the form of function.

[p45cal]

Yes, eg.:

for PD:

= Table.Skip(Quelle,each Text.Length(Text.Select([Column1],{"a".."z","A".."Z","0".."9"}))=0 or [Column1]=null)

edit: prompted by @dufoq3 's use of ?? (coalesce) we can lose the or [Column1]=null by:

= Table.Skip(Quelle,each Text.Length(Text.Select([Column1]?? "",{"a".."z","A".."Z","0".."9"}))=0)

For AP (keeping the headers row for promotion):

= Table.Skip(Quelle,List.PositionOf(Quelle[Column1],"#",Occurrence.First,each Text.StartsWith(_,"#"))-1)

[Goodkat]

Dear AlienSx and p45cal,

Thank you both for your replies. Until now I thought the 'Table.Skip' does only accept numbers as input parameter... It now is really fascinating to see that MS did think about a much smarter way of allowing also conditions as input!

I already thought developing the number of rows by an index & filter solution is elegant, but no... Smarter is to use it all directly in Table.Skip.

Thank you both for that tremendous insight!!!

Best regards, Andreas

Preview

[m_dekorte · Accepted solution]

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

[Goodkat]

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

[tayloramy]

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]

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

[jgeddes · Accepted solution]

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.

[Goodkat]

Dear Jgeddes,

Wow! It works so nicely and with such concise statement. I am honest, I had to read a bit more on List.Transform to fully understand its impact here. Now it is clear & I have learned.

Thank you so much for sharing your knowledge with me!

Have a good weekend & best regards, Andreas