Forum Discussion

abh1abh's avatar
abh1abh
Frequent Visitor
3 years ago

Grouping based on latest date

I have this table, make like this:

 

 

 

 

let
    Source = Sql.Database("mantleanalytics.database.windows.net", "Hevold_Dev"),
    dbo_rate_oslo_all = Source{[Schema="dbo",Item="rate_oslo_all"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_rate_oslo_all, each [Start_Date] <= Date_oslo and [Stop_Date] >= Date_oslo),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows"[[Port_of_Loading],[Shipping_Line],[40DC]],{"MSC","COSCO","YANGMING","HAPAG","CMA-NAK","CMA-CGM"},"Shipping_Line","40DC",each List.Min(_))
in
    #"Pivoted Column"

 

 

 

 

However, I now need to pivot based on latest date and grouped by "Shipping_Line", "Port_of_Loading", "Port_of_Discharge".
I have tried to do this:

 

 

 

let
    Source = Sql.Database("mantleanalytics.database.windows.net", "Hevold_Dev"),
    dbo_rate_oslo_all = Source{[Schema="dbo",Item="rate_oslo_all"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_rate_oslo_all, each [Start_Date] <= Date_oslo and [Stop_Date] >= Date_oslo),
    #"Filtered Rows1" = Table.Group(
        #"Filtered Rows", 
        {"Shipping_Line", "Port_of_Loading", "Port_of_Discharge","20DC"}, 
        {
            {"max_start_date", each List.Max([Start_Date]), type nullable date}
        }
    )
in
#"Filtered Rows"

 

 

 

 

But I cant get it to pivot based on the max date.

This how i tried it:

 

 

 

let
    Source = Sql.Database("mantleanalytics.database.windows.net", "Hevold_Dev"),
    dbo_rate_oslo_all = Source{[Schema="dbo",Item="rate_oslo_all"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_rate_oslo_all, each [Start_Date] <= Date_oslo and [Stop_Date] >= Date_oslo),
    #"Filtered Rows1" = Table.Group(
        #"Filtered Rows", 
        {"Shipping_Line", "Port_of_Loading", "Port_of_Discharge","20DC"}, 
        {
            {"max_start_date", each List.Max([Start_Date]), type nullable date}
        }
    ),
    #"Pivoted Column" = Table.Pivot(
        #"Filtered Rows1"[[Port_of_Loading],[Shipping_Line],[20DC]], 
        {"MSC","COSCO","YANGMING","HAPAG","CMA-NAK","CMA-CGM"},
        "Shipping_Line",
        "20DC",
        each List.Min(_)
    )
in
    #"Pivoted Column"

 

 

 

 

If there is any help i would really appreciate it

 

 

2 Replies

  • ppm1's avatar
    ppm1
    Solution Sage

    Grouping your data is a good thing to do if you only need summarized data; however, pivoting is usually not a good practice for analysis. Leave it unpivoted for easiest analysis, and pivot it in a matrix (by putting the date column on columns) when needed.

     

    Pat

     

    • abh1abh's avatar
      abh1abh
      Frequent Visitor

      I totally agree! However this is for presenting the data for a department. For analysis i usally work this other programs like python databases, but this department wants an excel file to acess the information