Forum Discussion

ceov95's avatar
ceov95
Regular Visitor
6 years ago
Solved

Retrieve a specific value with conditions

So I have this kind of data:

 

Item CodeQty StockDB Server DateMonth
Item 11020/3/2020March
Item 22015/3/2020March
Item 33018/3/2020March
Item 1823/3/2020March
Item 32428/3/2020March
Item 21517/3/2020March
Item 3203/4/2020April
Item 1510/4/2020April
Item 21216/4/2020April
Item 21025/4/2020April
Item 1426/4/2020April
Item 31829/4/2020April
Item 1215/5/2020May
Item 2719/5/2020May
Item 31219/5/2020May
Item 2426/5/2020May
Item 1126/5/2020May
Item 3927/5/2020May

 

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 CodeQty StockDB Server DateMonth
Item 1823/3/2020March
Item 32428/3/2020March
Item 21517/3/2020March
Item 21025/4/2020April
Item 1426/4/2020April
Item 31829/4/2020April
Item 2426/5/2020May
Item 1126/5/2020May
Item 3927/5/2020May

 

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. 

Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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.