Good day samahiji,

Yes, I believe it is possible to have all data for a specific car in one line. Here is an answer, perhaps inelegant, and late. I was travelling when your most recent reply came and was not in a position to give you an answer.

Continuing from my earlier solution the additional steps are:

1. Pivot on drive.
2. Add date columns for 2WD and 4WD.
3. Group by car.
4. Then I created a custom function. For each table in the group it transposes the table, merges the columns and then reverses the transpose. This has the effect of getting the data for a car in a one table row.
5. Then expand the table.
6. Form here on it is just tidying up – naming, typing and positioning columns.

Regards

M Code for custom function

(tbl as table ) as table =>
let
#"Transposed Table" = Table.Transpose(tbl),
#"Inserted Merged Column" = Table.AddColumn(#"Transposed Table", "Merged", each Text.Combine({Text.From([Column1], "en-GB"), Text.From([Column2], "en-GB")}, ""), type text),
#"Removed Other Columns2" = Table.SelectColumns(#"Inserted Merged Column",{"Merged"}),
#"Transposed Table1" = Table.Transpose(#"Removed Other Columns2")
in
#"Transposed Table1"

 M Code for query

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Car", type text}, {"Drive", type text}, {"Date", type date}, {"CaseNo", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Car", "Drive"}, {{"Latest Date", each List.Max([Date]), type nullable date}, {"All rows", each _, type table [Car=nullable text, CaseNo=nullable text, Drive=nullable text, Date=nullable date]}}),
#"Added CaseNo" = Table.AddColumn(#"Grouped Rows", "CaseNo", each List.Last( [All rows][CaseNo] ), type text ),
#"Removed Other Columns" = Table.SelectColumns(#"Added CaseNo",{"CaseNo", "Latest Date", "Drive", "Car"}),
#"END OF FIRST SOLUTION" = #"Removed Other Columns",
#"Pivoted drive" = Table.Pivot(#"END OF FIRST SOLUTION", List.Distinct(#"END OF FIRST SOLUTION"[Drive]), "Drive", "CaseNo", List.Max),
#"Add date column for 2WD" = Table.AddColumn(#"Pivoted drive", "2WD Date", each if ( [2WD] <> null ) then [Latest Date] else null),
#"Add date column for 4WD" = Table.AddColumn(#"Add date column for 2WD", "4WD Date", each if ( [4WD] <> null ) then [Latest Date] else null),
#"Group by car" = Table.Group(#"Add date column for 4WD", {"Car"}, {{"All Rows", each _, type table [Car=nullable text, 2WD=nullable text, 2WD Date=nullable date, 4WD=nullable text, 4WD Date=nullable date]}}),
#"Call custom function" = Table.TransformColumns( #"Group by car", {{"All Rows", each #"Collapse To One Row"(_) }} ),
Expand = Table.ExpandTableColumn(#"Call custom function", "All Rows", {"Column3", "Column4", "Column5", "Column6"}, {"Column3", "Column4", "Column5", "Column6"}),
#"TIDY UP" = Expand,
#"Rename columns" = Table.RenameColumns(#"TIDY UP",{{"Column3", "2WD CaseNo"}, {"Column4", "4WD CaseNo"}, {"Column5", "2WD Date"}, {"Column6", "4WD Date"}}),
#"Changed type" = Table.TransformColumnTypes(#"Rename columns",{{"Car", type text}, {"2WD CaseNo", Int64.Type}, {"4WD CaseNo", Int64.Type}, {"2WD Date", type date}, {"4WD Date", type date}}),
#"Reorder columns" = Table.ReorderColumns(#"Changed type",{"Car", "4WD Date", "4WD CaseNo", "2WD Date", "2WD CaseNo"})
in
#"Reorder columns"

Data

Output