Max date for subcategories

[Anonymous]

Hi

The table below shows when 4WD or 2WD was used for given car number.

It is required to show the latest 4WD and 2WD for each car.

The output table should looks like:

[collinsg]

Good day samahiji,

An approach is to,

1. Group by Car and Drive,  using the Advanced option and adding a new column called “Latest Date” as the Max of Date.
2. Pivot on the column Drive, using Latest Date as the values column.

Hope this helps.

Here is my data

Here is my output

Here is my M code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Car", type text}, {"Drive", type text}, {"Date", type date}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", 
       {"Car", "Drive"}, 
       {{"Latest Date", each List.Max([Date]), type nullable datetime}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", 
       List.Distinct(#"Grouped Rows"[Drive]), 
       "Drive", 
       "Latest Date", 
        List.Max)
in
    #"Pivoted Column

[Anonymous]

collinsg

Thanks a lot.

If I've generated two pivots one on on the column Drive and other on addional column, how do I combine both in one table (i.e join tables from two steps)

Output table should look like table below:

[collinsg]

samahiji,

By adding steps which

• Sort by date before grouping
• Including an "All Rows" column in the grouping
• Adding a step to return the last value in the CaseNo column of each table in the "All Rows" column

You can get to this data

From that point it is a case of loading the data into Excel and either using a pivot table like this,

or, if you wish for the exact layout you specify, using the GetPivotData formula.

This is my M code.

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"})
in
#"Removed Other Columns"

My starting data (at the "#Changed Type" step) looked like this.

[Anonymous]

Is it possible to have all data for specific car in one line? similar to the previously attached layout?

[collinsg]

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