Forum Discussion

jansaldi's avatar
jansaldi
Regular Visitor
5 years ago
Solved

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 ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    5 years ago

    jansaldi 

     

    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.

     

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

@jansaldi 

 

Filter the with previous 30 days should work.

 

Vpazhenmsft_1-1629079910947.png

Vpazhenmsft_2-1629079935957.png

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.