Forum Discussion
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 data I'm working with and what I'm aiming for it to do. Basically the function should label the highest versions for each unique id as "TRUE" and all non-highest versions for each unique id as "FALSE".
Thanks a lot for the assistance. It is much appreciated!
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
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
- Very elegant solution!
Thanks a lot for the assistance and thorough explanation. Much appreciated!MarcusNE1325 , you are welcome
