Forum Discussion

MarcusNE1325's avatar
MarcusNE1325
Copper Contributor
Aug 25, 2023
Solved

Power Query: Function that can identify highest Version number for each unique ID

Hi all,   I'm trying to create a column in power query that can identify the highest version number for each unique id and label them with TRUE/FALSE accordingly.   Below is an example of the dat...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 25, 2023

    MarcusNE1325 

    As variant

    - keep source table with only Id and VersionNo columns

    - create another query referencing the table

    - sort it ascending by Id and Descending by VersionNo

    - add Index column (any one, just to fix table in memory)

    - remove duplicated on Id column

    - add custom column (e.g. Flag) with true value in it

    - merge as new source with second query on Id and VersionNo

    - expand only Flag from column with tables

    - replace in resulting column null on false

    - use this resulting query

MarcusNE1325's avatar
MarcusNE1325
Copper Contributor
Aug 29, 2023
Very elegant solution!
Thanks a lot for the assistance and thorough explanation. Much appreciated!

Resources