Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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]
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.