Forum Discussion

pat_energetics's avatar
pat_energetics
Advocate II
8 years ago

Create new table with last complete record for each Unit

Hi,   I'm trying to create a new table containing only the latest record for each unique "DUID" in the column DUID of the table shown using the maximum value from the "LastChanged" column. The tab...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

pat_energetics

 

Try this calculated table

from Modelling Tab>>New Table

 

Calculated Table =
GENERATE (
    SELECTCOLUMNS ( VALUES ( 'Table1'[DUID] ), "DUID_", [DUID] ),
    CALCULATETABLE ( TOPN ( 1, 'Table1', [LastChanged], DESC ) )
)
pat_energetics's avatar
pat_energetics
Advocate II
8 years ago

Thanks Zubair_Muhammad, that seems very close.

 

There are still some duplicate DUID values returned (616 unique of 678 rows), and we are checking the source data to see if there are stray " " or similar, or where LastUpdate is the exact same time for DUIDs. I would have expected the TOPN 1 to resolve these types of issues ?