Add new column with latest update data

[evebarratt]

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 ID	VERSION NO	CLIENT NAME	** NEW COLUMN WITH LATEST UPDATE CLIENT NAME**
100100	1	JOHN SMITH	JON SMITH
100100	2	JON SMITH	JON SMITH
100111	1	MEGAN JONES	MEGAN JONES
100222	1	SALLY WAN	SALLY PETER WAN
100222	2	S P WAN	SALLY PETER WAN
100222	3	SALLY PETER WAN	SALLY PETER WAN
100333	1	EVE COLIN	EVA COLIN
100333	2	E COLIN	EVA COLIN
100333	3	EVA COLIN	EVA COLIN

Thank you.

[AlB]

Hi @evebarratt 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxBCoAgEAXQqwyuXaRzAomhDLVIKUK8/zWysCgL5q/e/xMjE02Tj3Emcoaxd+CtDj1L/GHytJqEKDNLnXKQC+QvlFIW9MqYDVbl3nTEw/QFvDcTBZqfBUQsT2khaEejKzr2v4DnRl2Udg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CLIENT ID" = _t, #"VERSION NO" = _t, #"CLIENT NAME" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CLIENT ID", Int64.Type}, {"VERSION NO", Int64.Type}, {"CLIENT NAME", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Max(Table.SelectRows(#"Changed Type", (inner)=>inner[CLIENT ID]=[CLIENT ID]),"VERSION NO")[CLIENT NAME])
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[evebarratt]

Thank you very much for your reply. From your query, this is what I got
Table.Max(Table.SelectRows(#"Changed Type", (inner)=>inner[CLIENT ID]=[CLIENT ID]),"VERSION NO")[CLIENT NAME]

and when I added this step into my actual file, it does not work.

By the way, my source is
"let
Source = Sql.Databases("$$-$$$-$$$.database.windows.net"),
#"sqldb-$$-$$$-rpt" = Source{[Name="sqldb-$$-$$$-rpt"]}[Data],

Please help!

[v-yingjl · Accepted solution]

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.