Forum Discussion
Anonymous
4 years agoNot applicable
How to not show dates after previous month
Hello,
I have the following table loaded in Power Query.
| Name | From | Until | Score |
| Kacy Jones | 18/11/2021 | 08/12/2021 | 49 |
| Kade Kennedy | 24/11/2021 | 16/12/2021 | 50 |
| Cristian Thompson | 20/12/2021 | 12/12/2021 | 49 |
| Kade Kennedy | 29/11/2021 | 09/12/2021 | 49 |
| Damian Mclellan | 17/12/2021 | 29/12/2021 | 52 |
| Damian Mclellan | 20/12/2021 | 26/12/2021 | 51 |
| Cristian Thompson | 25/12/2021 | 29/12/2021 | 52 |
| Ishan Rigby | 17/12/2021 | 29/12/2021 | 52 |
| Jimmie Harding | 19/12/2021 | 25/12/2021 | 51 |
| Brax Oneill | 26/12/2021 | 31/12/2021 | 52 |
| Kacy Jones | 26/12/2021 | 31/12/2021 | 52 |
| Bear Barry | 27/12/2021 | 02/01/2022 | 52 |
| Jorgie Galloway | 03/01/2022 | 09/01/2022 | 1 |
| Farah Lyon | 07/01/2022 | 26/01/2022 | 4 |
| Salman Goodman | 20/12/2021 | 10/01/2022 | 2 |
| Vincenzo Murray | 22/12/2021 | 11/01/2022 | 2 |
| Jorgie Galloway | 19/12/2021 | 09/01/2022 | 1 |
| Kade Kennedy | 06/01/2022 | 27/01/2022 | 4 |
| Kacy Jones | 10/01/2022 | 16/01/2022 | 2 |
| Kacy Jones | 16/01/2022 | 22/01/2022 | 3 |
| Kade Kennedy | 27/01/2022 | 16/02/2022 | 7 |
| Cristian Thompson | 23/01/2022 | 30/01/2022 | 4 |
| Kade Kennedy | 28/01/2022 | 17/02/2022 | 7 |
| Damian Mclellan | 04/02/2022 | 16/02/2022 | 7 |
| Damian Mclellan | 31/01/2022 | 06/02/2022 | 5 |
| Cristian Thompson | 26/01/2022 | 16/02/2022 | 7 |
| Ishan Rigby | 31/01/2022 | 21/02/2022 | 8 |
| Jimmie Harding | 31/01/2022 | 16/02/2022 | 7 |
| Brax Oneill | 27/01/2022 | 15/02/2022 | 7 |
| Kacy Jones | 30/11/2021 | 30/11/2021 | 48 |
| Bear Barry | 30/11/2021 | 30/11/2021 | 48 |
| Jorgie Galloway | 09/02/2022 | 20/02/2022 | 7 |
| Farah Lyon | 16/02/2022 | 09/03/2022 | 10 |
| Salman Goodman | 10/02/2022 | 27/02/2022 | 8 |
| Vincenzo Murray | 19/02/2022 | 10/03/2022 | 10 |
| Jorgie Galloway | 20/02/2022 | 25/02/2022 | 8 |
| Kade Kennedy | 21/02/2022 | 27/02/2022 | 8 |
| Kacy Jones | 21/02/2022 | 14/03/2022 | 11 |
| Kacy Jones | 21/02/2022 | 13/03/2022 | 10 |
| Kade Kennedy | 14/02/2022 | 20/02/2022 | 7 |
| Cristian Thompson | 04/03/2022 | 22/03/2022 | 12 |
| Kade Kennedy | 28/02/2022 | 06/03/2022 | 9 |
| Damian Mclellan | 06/03/2022 | 12/03/2022 | 10 |
| Damian Mclellan | 09/03/2022 | 30/03/2022 | 13 |
| Cristian Thompson | 06/03/2022 | 13/03/2022 | 10 |
| Ishan Rigby | 09/03/2022 | 30/03/2022 | 13 |
| Jimmie Harding | 14/03/2022 | 30/03/2022 | 13 |
| Brax Oneill | 11/03/2022 | 16/03/2022 | 11 |
| Kacy Jones | 18/03/2022 | 18/03/2022 | 11 |
| Bear Barry | 14/03/2022 | 20/03/2022 | 11 |
| Jorgie Galloway | 14/03/2022 | 04/04/2022 | 14 |
| Farah Lyon | 09/03/2022 | 30/03/2022 | 13 |
| Salman Goodman | 30/11/2021 | 30/11/2021 | 48 |
| Vincenzo Murray | 28/03/2022 | 08/04/2022 | 14 |
| Jorgie Galloway | 23/03/2022 | 30/03/2022 | 13 |
| Kade Kennedy | 17/03/2022 | 27/03/2022 | 12 |
| Kacy Jones | 21/03/2022 | 25/03/2022 | 12 |
| Kacy Jones | 01/04/2022 | 21/04/2022 | 16 |
| Kade Kennedy | 28/03/2022 | 15/04/2022 | 15 |
| Cristian Thompson | 30/03/2022 | 20/04/2022 | 16 |
| Kade Kennedy | 12/04/2022 | 03/05/2022 | 18 |
| Damian Mclellan | 10/04/2022 | 16/04/2022 | 15 |
| Damian Mclellan | 18/04/2022 | 27/04/2022 | 17 |
| Cristian Thompson | 01/05/2022 | 19/05/2022 | 20 |
| Salman Goodman | 10/05/2022 | 27/05/2022 | 18 |
| Vincenzo Murray | 09/04/2022 | 10/05/2022 | 21 |
| Jorgie Galloway | 20/04/2022 | 05/05/2022 | 26 |
| Kade Kennedy | 21/04/2022 | 27/05/2022 | 49 |
Here is the M code. The "Month End Date" is based only on the "Until" date column. But i do not know how to add some step/s to retain only the rows for which "Month End Date" which do not occur after the previous month, based on today's date. We are currently in April 2022, so the results should not include any rows with "Month End Date" as from 1 April 2022.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVfbbhMxEP2VVZ4rxeO9vwbUlpYKCRAvVR9MskpW2gtyQBC+Hnuh8YzHXvMWJ+d4zlyO7Tw/bx7V/pI9zFN33txsoNkCbKWQYBbCLOTromg3LzcWfeiyx26ausPFfCsLhIcK4Uux4N/o/vy9V1P2+TSP387zZEkC4cynZJAWi2o5/q0abYin/dANg7IhoEYoiSmljFCIKklSgXgqZSLOu/PJMD72x6+X/5H10I9j32X3Sh/66WgZGESi/VO10+pX9mHq+mHwdefAA5BuJ9G7Tulsp7Re2oDFC7kVS08kEj/roxF/p4Zh/qksReQIZTrnFn/F3yqtTtn7y1JLUaPfjTS3KBbwJzWMppZ383wYecdAYPZC+NJP+276PWdPP7Re9Eg8bGamfAbPgNQ/kIE3qgLLljXLgZoNS4aKqaFg8juufh70TO3tLV8XdXyYcbdyERBPQzQ4RM1CcIuJAoECojgjxz0SmFHG06jWU6eWJBEkIHQTdiQhBLb3DEn6UDI06bGpuTvoyKJouB+T6IAfWyTA2MdXQ/xIcrPM/JqHCBsS8I6yZrXkhgQsyNL9GDwHIluWLIg/pbCuiR6IGAwFlgMpdM7Fe1KgWK9+aJgFFmFt72LImCld0yqEj12VBASSZxGg4GHISdfyeCYkTqBa1JfJGPyqLBIM6kx7/DtElei1eRghQMPRxJpEihQcHbhoMMV2vXBzGLgrU9Vh3kyeFoHbEqdpHoNMUsCceUKXb4kaF6oOjbdnOIcuE2hz7jrFEi+gilnHbVhifPyyITnaTieCWINdIfZ5VLqhir1l6a5cV4CCm2XL6igrRw1gNS1ayJXzvsRh/GT4TNnBdWIIP+YMUlWDR5RwH8FL/oq3fxde/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, From = _t, Until = _t, Score = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"From", type date}, {"Until", type date}, {"Score", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"From"}),
Custom1 = Table.AddColumn(#"Removed Columns", "Month End Date", each Date.EndOfMonth([Until])),
#"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Month End Date", type date}})
in
#"Changed Type1"
Any help is much appreciated!
Insert this statement after Custom1
= Table.SelectRows(Custom1, each ([Month End Date]) < Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())))
1 Reply
- Vijay_A_Verma
Most Valuable Professional
Insert this statement after Custom1
= Table.SelectRows(Custom1, each ([Month End Date]) < Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())))