Forum Discussion

Alirezam's avatar
Alirezam
Helper V
5 years ago
Solved

Filter relative date in Query Editor

Hi,

Ref. the attached photo, in 'Query Editor' how can I filter the dates to exclude the 'current month'. How I can get rid of 'March' data while not mentioning the name of the month. Sound slike I can not have a ' before a Relative date'.

Thanks

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Alirezam 

     

    Download example PBIX

     

    Open the Advanced Editor and enter this step.  It checks the month in the Date column against the current month.

     

    #"Filtered Rows" = Table.SelectRows(#"Previous Step Name", each Date.Month([Date]) <> Date.Month(DateTime.Date(DateTime.LocalNow())))

     

     

    Here's an entire query for context - you'll find this in my sample PBIX above

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDQAhCEXRXlibMPzUXoz9tzEzBCVhyVlc3lpASMgPEzQggt0WcAqzi6SIuGiKqst3HzGLzpXeo8OlTCilwyn315UxXCwlNhPqkTmjo6UjKbHQUv49+wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.Month([Date]) <> Date.Month(DateTime.Date(DateTime.LocalNow())))
in
    #"Filtered Rows"

    Regards

    Phil

1 Reply

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Alirezam 

     

    Download example PBIX

     

    Open the Advanced Editor and enter this step.  It checks the month in the Date column against the current month.

     

    #"Filtered Rows" = Table.SelectRows(#"Previous Step Name", each Date.Month([Date]) <> Date.Month(DateTime.Date(DateTime.LocalNow())))

     

     

    Here's an entire query for context - you'll find this in my sample PBIX above

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDQAhCEXRXlibMPzUXoz9tzEzBCVhyVlc3lpASMgPEzQggt0WcAqzi6SIuGiKqst3HzGLzpXeo8OlTCilwyn315UxXCwlNhPqkTmjo6UjKbHQUv49+wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.Month([Date]) <> Date.Month(DateTime.Date(DateTime.LocalNow())))
in
    #"Filtered Rows"

    Regards

    Phil