cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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.

1 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]      
    )

DQ.png

 

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.

View solution in original post

3 REPLIES 3
Super User III
Super User III

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 

SU18_powerbi_badge

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!

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]      
    )

DQ.png

 

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.

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors