Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingTry 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks man it helped me a lot!!!!
Great @ceov95. Glad my solution helped you out.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.