Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have the following table coming in
Created Key
8/29/2023 6:42:28 PM A
7/26/2023 5:22:58 PM A
7/12/2023 9:00:01 AM A
6/27/2023 6:56:33 PM A
10/21/2023 7:52:22 PM B
What I need is the latest record only, so it would end up being
Created Key
8/29/2023 6:42:28 PM A
10/21/2023 7:52:22 PM B
Solved! Go to Solution.
Hi @EaglesTony ,
You can also consider using Dax to solve this problem:
Create calculated table.
Table 2 =
FILTER(
'Table',
'Table'[Ceated]=
MAXX(
FILTER(ALL('Table'),'Table'[Key]=EARLIER('Table'[Key])),[Ceated]))
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Created", type datetime}, {"Key", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {{"Count", each Table.Max(_,"Created")}}),
#"Expanded Count1" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Created"}, {"Created"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count1",{{"Key", type text}, {"Created", type datetime}})
in
#"Changed Type1"
Hope this helps.
Hi @EaglesTony ,
You can also consider using Dax to solve this problem:
Create calculated table.
Table 2 =
FILTER(
'Table',
'Table'[Ceated]=
MAXX(
FILTER(ALL('Table'),'Table'[Key]=EARLIER('Table'[Key])),[Ceated]))
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I got it.
1) Sort the field Created Descending
2) Wrap it around Table.Buffer:
= Table.Buffer(
Table.Sort(#"Changed Type",{{"CREATED", Order.Descending}}))
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |