Power Query - Excluding current month

[HakamAbushanab]

In the pursuit of precise and truthful data visualization, it sometimes becomes necessary to omit partial data that may not offer a complete picture. A common scenario where this practice is applicable is the representation of data for the current month, which is often not complete and can skew the comparison with the same period from the previous year.

To address this, we can strategically exclude the current month's data from our visualizations, thus enabling a more accurate year-over-year comparison. Leveraging the capabilities of Power Query M, we can accomplish this through the following script, applied to a field labeled “Date”:

= Table.SelectRows(#"Renamed Columns5", each not (Date.Year([Date]) = Date.Year(Date.From(DateTime.LocalNow())) and Date.Month([Date]) = Date.Month(Date.From(DateTime.LocalNow()))))

This script dynamically filters out the records pertaining to the current month, ensuring that our visualizations are both truthful and insightful, untainted by incomplete data.

Feel free to reach out with questions or to discuss this strategy further. Your feedback and insights are highly valued as we continue to refine our data representation methods. : )

[BA_Pete · Accepted solution]

Or, more simply:

Table.SelectRows(PreviousStep, each not Date.IsInCurrentMonth([Date]))

Pete

[HakamAbushanab]

Great job Pete, it is brand new function. According to the Power Query M documentation: https://learn.microsoft.com/en-us/powerquery-m/date-isincurrentmonth, the Date.IsInCurrentMonth function was created on July 19, 2023.

[BA_Pete]

No, that was the date the article was last updated.

Date.IsInCurrentMonth has been in the M language spec for many years.

Pete