Forum Discussion

evebarratt's avatar
evebarratt
Helper I
5 years ago
Solved

Add new column with latest update data

Hi there,

 

I am struggle to find the answer for what I want to achieve. I hope someone can help me please.

 

I have a table with a list of client which record of every version for their name change overtime. 

I want to add a new column and if the client has more than 1 version, just pick the latest name of their last version.

 

CLIENT IDVERSION NOCLIENT NAME** NEW COLUMN WITH LATEST UPDATE CLIENT NAME**

100100

1JOHN SMITHJON SMITH
100100

2

JON SMITHJON SMITH
1001111MEGAN JONESMEGAN JONES
1002221SALLY WANSALLY PETER WAN
1002222S P WANSALLY PETER WAN
1002223SALLY PETER WANSALLY PETER WAN
1003331EVE COLINEVA COLIN

100333

2E COLINEVA COLIN
1003333EVA COLINEVA COLIN


Thank you.

  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi evebarratt ,

    Seems like you are connecting to sql server with direct query mode, under direct query mode, multi query functions are limited which are familiar with dax situations. You could switch the mode from direct query to import mode and use the previous function or create a custom column like this:

    = Table.AddColumn(
        your table name in sql server,"new",
        each 
        let id = [CLIENT ID]
        in   
        Table.Last(
            Table.SelectRows(
                dbo_CLIENT_TABLE,
                each [CLIENT ID] = id
            )
        )[CLIENT NAME]      
    )

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.