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 ?

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    pat_energetics

     

    I think You can use DISTINCT on top of it to get rid of duplicates

     

    i.e.

     

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

      Unfortunately it is the ties in the [Lastchanged] column that is causing the TopN to return more than 1 row for some DUID.

       

      Would be useful if there was a parameter in TopN to explicitly return 1 row only - something similar to the RankX.

       

      Tried wrapping the function in LastNONBlank to explicitly return only 1 row, but this doesn't appear to work, probably something to do with operation on a column argument rather than a complete table row.

       

      For me, its quicker to simply export the 616 of 678 table out and remove the highlighted duplicates in excel. 

      • zapppsr's avatar
        zapppsr
        Kudo Collector
        8 years ago

        I don't know if it is ok for you to create an auxiliary column, but if it is ok, here is my solution:

         

        Create the ISMAX column to check if it is the last record.

        Then, create your virtual table with DISTINCT of only "Yes".