Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kimbtoth
Frequent Visitor

Return a Value based on other rows

I am trying to combine data from our old system with our new one. 
The commonality is the account number which will stay the same.  I want to update my spreadsheet so that if there was a date recorded at all for that account number, it will show for all rows for that same account number.  Also, I want it to show the most current date if there are multiple rows for the same account number with dates.  
For example, from the screenshot below, I want all dates for Account 1234 to show with most recent date of 4/5/2022.

Can someone help me figure out if this is possible?  Thanks so much!

kimbtoth_0-1663365974802.png

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @kimbtoth ;

You could try it.

vyalanwumsft_0-1663573259945.png

Then expand it and delete it.

vyalanwumsft_1-1663573319402.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYxBDgAhCAP/wlkSQJT4FuP/v+GJpdl4a6bT7k1q3amRiSkHK52GzNh5IMs8YfMJAUKksOp0Ve+SvUo9uIHR0Ug4nrP5o+cC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, #"Date ACE Completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", Int64.Type}, {"Date ACE Completed", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"maxdate", each List.Max([Date ACE Completed]), type nullable date}, {"a", each _, type table [Account Number=nullable number, Date ACE Completed=nullable date]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"Date ACE Completed"}, {"a.Date ACE Completed"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded a",{"a.Date ACE Completed"})
in
    #"Removed Columns"


Best Regards,
Community Support Team _ Yalan Wu
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

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @kimbtoth ;

You could try it.

vyalanwumsft_0-1663573259945.png

Then expand it and delete it.

vyalanwumsft_1-1663573319402.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYxBDgAhCAP/wlkSQJT4FuP/v+GJpdl4a6bT7k1q3amRiSkHK52GzNh5IMs8YfMJAUKksOp0Ve+SvUo9uIHR0Ug4nrP5o+cC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, #"Date ACE Completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", Int64.Type}, {"Date ACE Completed", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"maxdate", each List.Max([Date ACE Completed]), type nullable date}, {"a", each _, type table [Account Number=nullable number, Date ACE Completed=nullable date]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"Date ACE Completed"}, {"a.Date ACE Completed"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded a",{"a.Date ACE Completed"})
in
    #"Removed Columns"


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

 

kimbtoth
Frequent Visitor

Hi @mussaenda ,
Thanks for getting back to me!  I will google to figure that out since I don't know how to extract the max date.  If I group, don't I lose all the other columns except that column that I am grouped on? 

Hi @kimbtoth ,

Exactly what @v-yalanwu-msft showed you.

 

Let us know if you need more help about this topic.

 

mussaenda
Super User
Super User

Hi @kimbtoth ,

 

you can group them by account number then extract the max date ace completed.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors