none
Table.PositionOf (Accessing position of record ) RRS feed

  • Question

  • Hi there!

    I have the following code that doesnt appear to work.

    If I put an "=" after "From State" as opposed to "<>", then the step seems to execute as seen below:

    But that's not what I want PQ to do. I would like to have the position of the last occurence in table where the the two columns have different data in each of the rows.


    Here is what the [All] table looks like with the respective columns:


    Any help would be great!

    -Sam

    Thursday, October 3, 2019 3:24 PM

Answers

  • I'm fairly certain Table.PositionOf can only find a matching record. However we can find the last instance of non-matching column values and use those to get our match.

    #"Added Custom" = Table.AddColumn(#"Replaced Value3", "Custom", each 
    List.Last
        (
        Table.PositionOf
            (
            [All], 
            Table.LastN
                (
                Table.SelectRows
                    (
                    [All], 
                    each ([From State] <> [To State])
                    ),
                1
                ){0},
            Occurrence.All
            )
        )

    I don't know if this is the most optimal way to do this, but the breakdown is:

    1. Filter the table to rows where the [From State] and [To State] don't match
    2. Get the last record from this filtered table
    3. Search for this record with Table.PositionOf and Occurrence.All, which returns a list of every position matching this record
    4. Get the last item out of this list for your final position
    • Marked as answer by SamNaik Thursday, October 3, 2019 9:46 PM
    Thursday, October 3, 2019 5:18 PM

All replies

  • I'm fairly certain Table.PositionOf can only find a matching record. However we can find the last instance of non-matching column values and use those to get our match.

    #"Added Custom" = Table.AddColumn(#"Replaced Value3", "Custom", each 
    List.Last
        (
        Table.PositionOf
            (
            [All], 
            Table.LastN
                (
                Table.SelectRows
                    (
                    [All], 
                    each ([From State] <> [To State])
                    ),
                1
                ){0},
            Occurrence.All
            )
        )

    I don't know if this is the most optimal way to do this, but the breakdown is:

    1. Filter the table to rows where the [From State] and [To State] don't match
    2. Get the last record from this filtered table
    3. Search for this record with Table.PositionOf and Occurrence.All, which returns a list of every position matching this record
    4. Get the last item out of this list for your final position
    • Marked as answer by SamNaik Thursday, October 3, 2019 9:46 PM
    Thursday, October 3, 2019 5:18 PM
  • Modified your query slightly and I am good to go :)
    Thanks a lot!

    -Sam

    Thursday, October 3, 2019 9:46 PM