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.

Need Help
  • 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.

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      evebarratt
      Helper I
      5 years ago

      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's avatar
        v-yingjl
        Community Support
        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.