Group by Id, category and show last value in the month Power Query

[Tazmastablasta]

Hi 

Here is my problem. I have a data set that has ID , Category , Date Submitted and the score.

Is there a way to group those scores by ID, Category, Month and show last value(the last submitted by date) in the month for each of the months ? 

I have tried to follow this turorial 

https://radacad.com/grouping-in-power-query-getting-the-last-item-in-each-group.

But the result I get is Max value not last value.

Here is an example :

This score is the Max score of this category not the last.

The last score should be zero as seen here

Here is the the query I have used

Query

Same happens when if I only leave the month column.

I would appreciate any help, and thank you in advance.

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

please also include the M code that doesn't work for you

[Tazmastablasta]

Hi Stachu 

Thank you so much for your replay.

Here is the M code I have used 

let
    Source = Excel.Workbook(File.Contents("Sample.xlsx"), null, true),
    Sample_Sheet = Source{[Item="Sample",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sample_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CustomerId", Int64.Type}, {"ResponseFamily", type text}, {"Scoring", Int64.Type}, {"ResponseDate", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([ResponseDate]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([ResponseDate]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Month", type text}, {"Year", type text}}, "en-US"),{"Month", "Year"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Month.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Month.1", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"ResponseDate", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CustomerId", "ResponseFamily", "Month.1"}, {{"Date", each List.Max([ResponseDate]), type date}, {"Table", each _, type table [CustomerId=number, ResponseFamily=text, Scoring=number, ResponseDate=date, Month.1=date]},{"Last Score", each List.Last([Scoring]), type number}}),
    #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"CustomerId", Order.Descending}})
in
    #"Sorted Rows1"

And here is the link for the sample file 

https://www.dropbox.com/s/s7s5eupal5ecpjz/Sample.xlsx?dl=0

Thank you for your time. 

[Stachu · Accepted solution]

the code worked fine the only problem was that the sort was Descending with List.Last

I changed it to Ascending, and it works fine

let
    Source = Excel.Workbook(File.Contents("Sample.xlsx"), null, true),
    Sample_Sheet = Source{[Item="Sample",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sample_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CustomerId", Int64.Type}, {"ResponseFamily", type text}, {"Scoring", Int64.Type}, {"ResponseDate", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([ResponseDate]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([ResponseDate]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Month", type text}, {"Year", type text}}, "en-US"),{"Month", "Year"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Month.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Month.1", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"ResponseDate", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CustomerId", "ResponseFamily", "Month.1"}, {{"Date", each List.Max([ResponseDate]), type date}, {"Table", each _, type table [CustomerId=number, ResponseFamily=text, Scoring=number, ResponseDate=date, Month.1=date]},{"Last Score", each List.Last([Scoring]), type number}}),
    #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"CustomerId", Order.Descending}})
in
    #"Sorted Rows1"

and it seems to work fine, but you can keep the Descending sort and use List.First instead

[Tazmastablasta]

Hi 

Thank you so much, it worked like a charm.