Retrieve a specific value with conditions

[ceov95]

So I have this kind of data:

Item Code	Qty Stock	DB Server Date	Month
Item 1	10	20/3/2020	March
Item 2	20	15/3/2020	March
Item 3	30	18/3/2020	March
Item 1	8	23/3/2020	March
Item 3	24	28/3/2020	March
Item 2	15	17/3/2020	March
Item 3	20	3/4/2020	April
Item 1	5	10/4/2020	April
Item 2	12	16/4/2020	April
Item 2	10	25/4/2020	April
Item 1	4	26/4/2020	April
Item 3	18	29/4/2020	April
Item 1	2	15/5/2020	May
Item 2	7	19/5/2020	May
Item 3	12	19/5/2020	May
Item 2	4	26/5/2020	May
Item 1	1	26/5/2020	May
Item 3	9	27/5/2020	May

And I need to find for each item, and for each month, the max date so I can retrieve the stock quantity, like this:

Item Code	Qty Stock	DB Server Date	Month
Item 1	8	23/3/2020	March
Item 3	24	28/3/2020	March
Item 2	15	17/3/2020	March
Item 2	10	25/4/2020	April
Item 1	4	26/4/2020	April
Item 3	18	29/4/2020	April
Item 2	4	26/5/2020	May
Item 1	1	26/5/2020	May
Item 3	9	27/5/2020	May

I've tried doing in a way but was successful just for one Item but when I removed the filter for all the items it only brings me the max date without considering the Item Code. 

[edhans · Accepted solution]

Try this code. It returns this table. I just happened to write about this in a blog post this week.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZLLCsIwEEV/RbIuJJ1pTLt06cIvKF2ICAoKUtz49/ZeH6XizGYIyeE+hvR92N6P11UdqlCnaUiKGiUJzrv9eDiFoXozwmeA2WR0ulIyrcnAq4WWujLSYNgywiQYxdfBvcbmg2xu4/myTEOVZCJ04lj7DBeYXSuWsmWUqwPTuTKv8jHPvR/LLAVAZwD6LWQRMkf9D/DDeAA8OgDlBxie", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Code" = _t, #"Qty Stock" = _t, #"DB Server Date" = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Qty Stock", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"DB Server Date", type date}}, "en-BS"),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type with Locale", 
            {"Item Code", "Month"}, 
            {
                {"Latest Date", each List.Max([DB Server Date]), type nullable date}, 
                {"Stock Amount", each Table.Max(_, "DB Server Date")[Qty Stock], Int64.Type}
            }
        )


in
    #"Grouped Rows"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[ceov95]

Thanks man it helped me a lot!!!!

[edhans]

Great @ceov95. Glad my solution helped you out.