displaying data from the last two records per group in a single row

[martin65]

I have a table where the latest position  comments on  projects are stored , we want to see a table with the latest and previous  entries are shown. 

Id                    date                comment                    position

group id 1	01/01/2024	latest comment 1	position 1
group id 2	01/01/2024	latest comment 1	position 1
group id 1	02/01/2024	latest comment 2	position 2
group id 2	02/01/2024	latest comment 2	position 2
group id 1	03/01/2024	latest comment 3	position 3
group id 2	03/01/2024	latest comment 3	position 3
group id 1	04/01/2024	latest comment 4	position 4
group id 2	04/01/2024	latest comment 4	position 4

 to be displayed as 

id                     latest date        current position                                  previous position

group id 1	04/01/2024	latest comment 4 & position 4	latest comment 3 and position 3
group id 1	04/01/2024	latest comment 4 & position 4	latest comment 3 and position 3

If it was in a SQL source i'd do it on the way in with the query but i need to do it in power query or dax  in the report itself

[dufoq3]

Hi @martin65,

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pc1NCoAgEIbhq4jrIB29jbiIkhDyh5zun7iahS4ymM3H8PAaw887PZn5g0m+cCHXeiBA13Ft6AqyPYXgIrZ/TsWjT7EOuxALP2zrwtgCtdDpTtrWVWOrqFWd7qRtXT22mlrd6X6w9gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, comment = _t, position = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
      {"latest Date", each List.Max([date]), type date},
      {"current position", each Text.Combine(Record.ToList(Table.Last(Table.Sort(_, {"date", Order.Ascending}), 1)[[comment], [position]]), " & "), type text},
      {"previous  position", each Text.Combine(Record.ToList(Table.Last(Table.RemoveLastN(Table.Sort(_, {"date", Order.Ascending}), 1), 1)[[comment], [position]]), " & "), type text}
  }  )
in
    #"Grouped Rows"

[martin65]

thank you for that it seemed to make sense  but i generate an error when i try to apply it 

Expression.Error: The specified sort criteria is invalid.
Details:
[List]

I have plugged it into the full table query as shown , which is probably why I've generated the error

let
Source = Cds.Entities(#"Environment URL (2)", [ReorderColumns=null, UseFormattedValue=null]),
entities = Source{[Group="entities"]}[Data],
msdyn_projectstatusreports = entities{[EntitySetName="msdyn_projectstatusreports"]}[Data],
#"Renamed Columns" = Table.RenameColumns(msdyn_projectstatusreports,{{"msdyn_plannedactivities", "Planned Activities"}, {"msdyn_additionalcomments", "Additional Comments"}, {"msdyn_accomplishedactivities", "Accomplished Activities"}, {"msdyn_name", "Status Name"}, {"msdyn_project", "Project"}, {"proj_reportingdate", "Reporting Date"}}),
#"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"msdyn_projectstatusreportid", "Status Reports ID"}, {"proj_submittedto", "Submitted To"}, {"proj_submitter", "Submitter"}}),

#"Grouped Rows" = Table.Group(#"Renamed Columns1", {"Project"}, {
{"latest Date", each List.Max([modifiedon]), type date},
{"current position", each Text.Combine(Record.ToList(Table.Last(Table.Sort(_, {"latest Date", Order.Ascending}), 1)[[Accomplished Activities], [Additional Comments]]), " & "), type text},
{"previous position", each Text.Combine(Record.ToList(Table.Last(Table.RemoveLastN(Table.Sort(_, {"latest Date", Order.Ascending}), 1), 1)[[Accomplished Activities], [Additional Comments]]), " & "), type text}
} )
in
#"Grouped Rows"

[dufoq3]

@martin65 could you send me a screenshot of your table at point Renamed Columns1?

[martin65]

[m_dekorte]

Hi @martin65,

Group By is well suited for a requirement like this, consider the following: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pc1NCoAgEIbhq4jrIB29jbiIkhDyh5zun7iahS4ymM3H8PAaw887PZn5g0m+cCHXeiBA13Ft6AqyPYXgIrZ/TsWjT7EOuxALP2zrwtgCtdDpTtrWVWOrqFWd7qRtXT22mlrd6X6w9gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, date = _t, comment = _t, position = _t]),
    GroupRows = Table.Group(Source, {"Id"}, 
        {
            {"latest date", each List.Max([date]), type nullable text}, 
            {"t", each [
                    current postition= Text.Combine( List.LastN( List.Last(Table.ToRows(_)), 2), " & "), 
                    previous postition = Text.Combine( List.LastN( List.Reverse(Table.ToRows(_)){1}?, 2), " & ")
                ], type [current postition = text, previous postition= text] 
            }
        }
    ),
    ExpandRecord = Table.ExpandRecordColumn(GroupRows, "t", {"current postition", "previous postition"})
in
    ExpandRecord

This solution does assume "comment" and "position" are the last two columns in your table.

With this result

I hope this is helpful