Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Max date for subcategories

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:

Need Help

5 Replies

  • collinsg's avatar
    collinsg
    Solution Sage
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        collinsg
        Solution Sage
        3 years ago

        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.