Forum Discussion
Date Filtering in Power Query
Hi all, I am having trouble figuring out how to filter a date column to show me everything that falls 30 days before the current date AND blank cells and it needs to be dynamic so that each time the Power query is run it will show me from whatever date it is run. Please see below for the column date in question.
So basically it needs to to filter to show anything that is blank OR that is 30 days or more before the current date.
Thanks for any help that can be offered!
| Last Eligibility Upload |
| 5/19/2021 |
| 6/14/2021 |
| 7/12/2021 |
| 8/3/2021 |
| 6/11/2021 |
| 6/16/2021 |
| 6/14/2021 |
| 5/27/2021 |
| 6/1/2021 |
| 6/14/2021 |
| 7/29/2021 |
| 7/15/2021 |
| 7/16/2021 |
| 6/29/2021 |
| 7/14/2021 |
| 6/14/2021 |
| 8/10/2021 |
| 7/16/2021 |
| 6/16/2021 |
| 7/22/2021 |
| 7/22/2021 |
| 5/7/2021 |
| 7/14/2021 |
| 6/30/2021 |
| 6/1/2021 |
| 2/17/2021 |
| 6/15/2021 |
| 6/29/2021 |
| 8/2/2021 |
This can be done with an expression like the one below. Just create a Filter step on that column for any date filter, and then modify the code in the formula bar to match this pattern.
= Table.SelectRows(#"Changed Type", each [Last Eligibility Upload] = null or [Last Eligibility Upload] <= Date.AddDays(Date.From(DateTime.LocalNow()), -30))
Pat
Filter the with previous 30 days should work.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJBCsMwDMC+MnIuxHbWVdtXSv//jV7WMlaN3YIIGNla11ZR2Zc+bvGKaFPLtk0nvSudlT6ULkpR+lSa4Tgdl2P3SxdMN0xXTHdMl8wvy+vj/bHcpNyk3KTmf9N+zh++92F7pzs1BbQ1tDW0NbQ1tDW0NbQ1vDW8Nbw1vDW8Nbw1vDW8Nbw1Lq0dV/m03HY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"1", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Date], 30) or [Date] = null) in #"Filtered Rows"Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
4 Replies
This can be done with an expression like the one below. Just create a Filter step on that column for any date filter, and then modify the code in the formula bar to match this pattern.
= Table.SelectRows(#"Changed Type", each [Last Eligibility Upload] = null or [Last Eligibility Upload] <= Date.AddDays(Date.From(DateTime.LocalNow()), -30))
Pat
Filter the with previous 30 days should work.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJBCsMwDMC+MnIuxHbWVdtXSv//jV7WMlaN3YIIGNla11ZR2Zc+bvGKaFPLtk0nvSudlT6ULkpR+lSa4Tgdl2P3SxdMN0xXTHdMl8wvy+vj/bHcpNyk3KTmf9N+zh++92F7pzs1BbQ1tDW0NbQ1tDW0NbQ1vDW8Nbw1vDW8Nbw1vDW8Nbw1Lq0dV/m03HY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"1", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Date], 30) or [Date] = null) in #"Filtered Rows"Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.Filter the with previous 30 days should work.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJBCsMwDMC+MnIuxHbWVdtXSv//jV7WMlaN3YIIGNla11ZR2Zc+bvGKaFPLtk0nvSudlT6ULkpR+lSa4Tgdl2P3SxdMN0xXTHdMl8wvy+vj/bHcpNyk3KTmf9N+zh++92F7pzs1BbQ1tDW0NbQ1tDW0NbQ1vDW8Nbw1vDW8Nbw1vDW8Nbw1Lq0dV/m03HY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"1", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Date], 30) or [Date] = null) in #"Filtered Rows"Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.Filter the with previous 30 days should work.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJBCsMwDMC+MnIuxHbWVdtXSv//jV7WMlaN3YIIGNla11ZR2Zc+bvGKaFPLtk0nvSudlT6ULkpR+lSa4Tgdl2P3SxdMN0xXTHdMl8wvy+vj/bHcpNyk3KTmf9N+zh++92F7pzs1BbQ1tDW0NbQ1tDW0NbQ1vDW8Nbw1vDW8Nbw1vDW8Nbw1Lq0dV/m03HY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"1", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Date], 30) or [Date] = null) in #"Filtered Rows"Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
