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
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!
Solved! Go to Solution.
Hi, @kimbtoth ;
You could try it.
Then expand it and delete it.
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.
Hi, @kimbtoth ;
You could try it.
Then expand it and delete it.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
64 | |
27 | |
18 | |
13 |