Leave only latest row in Query

[Anonymous]

Hi,

When the titles are the same(1 and 2 same, 3 and 4 same), I wanna only get latest ReportedTime from each Titles.

Sometimes we have last minute changes so reporters adjust it and re-upload with the same file name, then I need only latest. 

It's cumulative reports every week with Year & Week title,

They will be always 2 reporters so I cannot do descending order with leave top row.  

[MasonMA · Accepted solution]

Hi, 

With UI, you can first Sort by 'ResponseTime' descending, Group by 'Title' with 'All Rows', then in your fx 'Table.Group' M code, use Table.FirstN(_, 1) to replace '_' after 'each'. 

In Query editor, this is the entire UI generated code. 

let
    Source = table,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Response", type text}, {"ReportedTime", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ReportedTime", Order.Descending}}),
    #"Grouped Rows" = Table.Group(
    #"Sorted Rows", 
    {"Title"}, 
    {
        {"LatestRow", each Table.FirstN(_, 1), type table [Title=nullable text, Response=nullable text, ReportedTime=nullable datetime]}
    }
),
    #"Expanded LatestRow" = Table.ExpandTableColumn(#"Grouped Rows", "LatestRow", {"ReportedTime"}, {"ReportedTime"})
in
    #"Expanded LatestRow"

[Ilgar_Zarbali · Accepted solution]

Simplified & Optimized Solution

You can skip expanding and directly extract the fields you want:

let
Source = table,
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Title", type text}, {"Response", type text}, {"ReportedTime", type datetime}}),
#"Grouped Rows" = Table.Group(
Source,
{"Title"},
{
{"LatestRow", each Table.Max(_, "ReportedTime"), type record}
}
),
#"Expanded LatestRow" = Table.ExpandRecordColumn(#"Grouped Rows", "LatestRow", {"Response", "ReportedTime"})
in
#"Expanded LatestRow"

Key Points:

• Instead of sorting + Table.FirstN, you can directly use Table.Max with "ReportedTime" → it will automatically return the row with the latest timestamp.
• Table.Max(_, "ReportedTime") avoids unnecessary sorting (faster on large datasets).
• Expanding a record is cleaner than expanding a nested table.

Result:

You’ll get one row per Title, containing:

• Title
• Response (from latest row)
• ReportedTime (latest timestamp)

DAX version of this “latest record by group” logic

1-Latest ReportedTime by Title

This measure gives you the maximum ReportedTime for the current Title in a visual:

Latest ReportedTime =
CALCULATE (
MAX ( TableName[ReportedTime] ),
ALLEXCEPT ( TableName, TableName[Title] )
)

Use case:

• Place Title in a table visual.
• Add this measure → it shows the latest timestamp per Title.

2 - Latest Response by Title

If you also want the Response value from that latest row:

Latest Response =
VAR LatestDate =
CALCULATE (
MAX ( TableName[ReportedTime] ),
ALLEXCEPT ( TableName, TableName[Title] )
)
RETURN
CALCULATE (
MAX ( TableName[Response] ),
TableName[ReportedTime] = LatestDate
)

Use case:

• Place Title, Latest ReportedTime, and Latest Response in a table visual.
• It will show exactly the “latest row per Title”.

3 - Virtual Table

If you want to create a table (not a measure), you can use:

Latest Records =
SUMMARIZECOLUMNS (
TableName[Title],
"Latest ReportedTime",
CALCULATE (
MAX ( TableName[ReportedTime] )
),
"Latest Response",
CALCULATE (
MAXX (
TOPN (
1,
TableName,
TableName[ReportedTime], DESC
),
TableName[Response]
)
)
)

Use case:

• This creates a calculated table with one row per Title, holding the latest record.

Your kudos is highly appreciated. If it helps you to solve your challenge, I would be happy if you accept it as a solution.

[ryan_mayu · Accepted solution]

@Anonymous 

is this what you want?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXIsKCjKL0sFsowMjEz1DfUNFQwsrAwsrQyNlGJ1cKoxNLIyNLQyMgarSQLKBKVmpSaXIJQYK5hYGZlbmRrAVWCYAlZiYWUMVBILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, respone = _t, ReportedTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"respone", type text}, {"ReportedTime", type datetime}}),
SortedRows = Table.Sort(#"Changed Type" ,{{"Title", Order.Ascending}, {"ReportedTime", Order.Descending}}),
RemovedDuplicates = Table.Distinct(SortedRows, {"Title"})
in
RemovedDuplicates

pls see the attachment below

it this is not what you want, pls provide the expected output based on the sample data you provided

[danextian · Accepted solution]

Hi @Anonymous 

Do these:

• Sort by Reported Time in descending order.
• Add a custom step and wrap that Table.Buffer - this is necessary in order to store the sorted table into memory otherwise you will get an unexpected result in the next step. Example: Table.Buffer (#"Sorted Column")
• Right click Title and then remove duplicates.

[ryan_mayu · Accepted solution]

@Anonymous 

is this what you want?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXIsKCjKL0sFsowMjEz1DfUNFQwsrAwsrQyNlGJ1cKoxNLIyNLQyMgarSQLKBKVmpSaXIJQYK5hYGZlbmRrAVWCYAlZiYWUMVBILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, respone = _t, ReportedTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"respone", type text}, {"ReportedTime", type datetime}}),
SortedRows = Table.Sort(#"Changed Type" ,{{"Title", Order.Ascending}, {"ReportedTime", Order.Descending}}),
RemovedDuplicates = Table.Distinct(SortedRows, {"Title"})
in
RemovedDuplicates

pls see the attachment below

it this is not what you want, pls provide the expected output based on the sample data you provided

[Ilgar_Zarbali · Accepted solution]

Simplified & Optimized Solution

You can skip expanding and directly extract the fields you want:

let
Source = table,
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Title", type text}, {"Response", type text}, {"ReportedTime", type datetime}}),
#"Grouped Rows" = Table.Group(
Source,
{"Title"},
{
{"LatestRow", each Table.Max(_, "ReportedTime"), type record}
}
),
#"Expanded LatestRow" = Table.ExpandRecordColumn(#"Grouped Rows", "LatestRow", {"Response", "ReportedTime"})
in
#"Expanded LatestRow"

Key Points:

• Instead of sorting + Table.FirstN, you can directly use Table.Max with "ReportedTime" → it will automatically return the row with the latest timestamp.
• Table.Max(_, "ReportedTime") avoids unnecessary sorting (faster on large datasets).
• Expanding a record is cleaner than expanding a nested table.

Result:

You’ll get one row per Title, containing:

• Title
• Response (from latest row)
• ReportedTime (latest timestamp)

DAX version of this “latest record by group” logic

1-Latest ReportedTime by Title

This measure gives you the maximum ReportedTime for the current Title in a visual:

Latest ReportedTime =
CALCULATE (
MAX ( TableName[ReportedTime] ),
ALLEXCEPT ( TableName, TableName[Title] )
)

Use case:

• Place Title in a table visual.
• Add this measure → it shows the latest timestamp per Title.

2 - Latest Response by Title

If you also want the Response value from that latest row:

Latest Response =
VAR LatestDate =
CALCULATE (
MAX ( TableName[ReportedTime] ),
ALLEXCEPT ( TableName, TableName[Title] )
)
RETURN
CALCULATE (
MAX ( TableName[Response] ),
TableName[ReportedTime] = LatestDate
)

Use case:

• Place Title, Latest ReportedTime, and Latest Response in a table visual.
• It will show exactly the “latest row per Title”.

3 - Virtual Table

If you want to create a table (not a measure), you can use:

Latest Records =
SUMMARIZECOLUMNS (
TableName[Title],
"Latest ReportedTime",
CALCULATE (
MAX ( TableName[ReportedTime] )
),
"Latest Response",
CALCULATE (
MAXX (
TOPN (
1,
TableName,
TableName[ReportedTime], DESC
),
TableName[Response]
)
)
)

Use case:

• This creates a calculated table with one row per Title, holding the latest record.

Your kudos is highly appreciated. If it helps you to solve your challenge, I would be happy if you accept it as a solution.

[MasonMA · Accepted solution]

Hi, 

With UI, you can first Sort by 'ResponseTime' descending, Group by 'Title' with 'All Rows', then in your fx 'Table.Group' M code, use Table.FirstN(_, 1) to replace '_' after 'each'. 

In Query editor, this is the entire UI generated code. 

let
    Source = table,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Response", type text}, {"ReportedTime", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ReportedTime", Order.Descending}}),
    #"Grouped Rows" = Table.Group(
    #"Sorted Rows", 
    {"Title"}, 
    {
        {"LatestRow", each Table.FirstN(_, 1), type table [Title=nullable text, Response=nullable text, ReportedTime=nullable datetime]}
    }
),
    #"Expanded LatestRow" = Table.ExpandTableColumn(#"Grouped Rows", "LatestRow", {"ReportedTime"}, {"ReportedTime"})
in
    #"Expanded LatestRow"