Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to create a filter in power query that will show me only items that are created on or after the 27th day of the previous month until "today".
For example, today is July 26th. The query would show me only items created from June 27th
Solved! Go to Solution.
Hi @jcambareri
You have a date column you can filter, right? Here is one way, provide sample data in a format which people can copy in the future
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdJLCgMxDATRu3g9MFLL37MMs0hy/zvEziY4lW3BA0HrutIjHcnHafWUydN9XOk5k2xLr5V8SwtKhEGYCQthJWyEnXAAhgHavL7t0LSlBS229IGZsBBWwkbYCQegG6A7oIswCDNhIayEjbATDkAZoBxQIgzCTFgIK2Ej7IQDMAww/nzOTP1nR33T/QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each
[Date] >= Date.AddDays( Date.AddMonths( Date.StartOfMonth(Date.From( DateTime.LocalNow())),-1),26)
and
[Date] <= Date.From( DateTime.LocalNow()))
in
#"Filtered Rows"
Hi @jcambareri,
Assuming you have a Date column and the data type is Date,
You can do the filtering and select last month.
It will automatically filter your data for the past 1 month.
Hope this idea helps.
Hi @jcambareri
You have a date column you can filter, right? Here is one way, provide sample data in a format which people can copy in the future
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdJLCgMxDATRu3g9MFLL37MMs0hy/zvEziY4lW3BA0HrutIjHcnHafWUydN9XOk5k2xLr5V8SwtKhEGYCQthJWyEnXAAhgHavL7t0LSlBS229IGZsBBWwkbYCQegG6A7oIswCDNhIayEjbATDkAZoBxQIgzCTFgIK2Ej7IQDMAww/nzOTP1nR33T/QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each
[Date] >= Date.AddDays( Date.AddMonths( Date.StartOfMonth(Date.From( DateTime.LocalNow())),-1),26)
and
[Date] <= Date.From( DateTime.LocalNow()))
in
#"Filtered Rows"