none
Date filter for X previous Hours in Excel Query RRS feed

  • General discussion

  • Hi,

    is anyone can help me in excel power Query? i have to filter datatime column for "x" previous hours. but it's showing only day, month, year option in excel but power Bi have option for Hours as well.

    can anyone help me how i can do it in excel query?

    Thanks 

    Ilyas

    Monday, September 10, 2018 9:39 AM

All replies

  • Hey,

    What version of Power Query are you using?

    When I try doing this in Power Query inside of Power BI Desktop I actually do get the correct drop down list to do the filter by the last x hours, minutes or even seconds:

    

    The code that you're looking for looks something like this:

    = Table.SelectRows(#"Changed Type", each DateTime.IsInPreviousNMinutes([Column1], 3))

    Tuesday, September 11, 2018 12:36 AM
  • Hi Miguel,

    Actually my power BI query having all options that you have shown. but i'm talking about query that we we open through excel (Data tab> Get & Transform> show query). excel query dont have this option. how i can manage in excel?

    thanks

    ilyas

    Tuesday, September 11, 2018 5:22 AM
  • Could you share what version of Excel 2016 you’re using? Worst case scenario, if your Excel 2016 doesn’t have it then you’ll have to write the code manually as showcased in my previous reply
    Tuesday, September 11, 2018 5:47 AM
  • Version is 2016 MSO (16.0.4266.1001) 64 bit
    Sunday, September 16, 2018 8:39 AM
  • Getting this error while write code as u mentioned 

    Sunday, September 16, 2018 8:44 AM
  • Version is 2016 MSO (16.0.4266.1001) 64 bit

    that's what I was afraid of. That seems to be the MSI version of Office, which hasn't received any updates in a long time.

    The feature that you're referring to is relatively new, so even the code doesn't even exist in your version of Excel. I can confirm that this does exist in the Office 365 version of Microsoft Office Pro+.

    What you'll need to do is replicate that functionality somehow like in the code below:

    let
        Source = DateTime.LocalNow(),
        #"Converted to Table" = #table(1, {{Source}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(2018,9,17,8,42,0)),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}}),
        #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each [Column1] - [Custom], type duration)
    in
        #"Inserted Time Subtraction"

    in the end, that new "Subtraction" column could be filtered so you only get the dates that are "x" amount of minutes within the last refresh time. You could create your own function based on that concept and make it a bit more elegant. 

    Monday, September 17, 2018 1:45 PM
  • it's little bit complete for us.

    Any possibility to have code in my excel version?

    Sunday, September 23, 2018 11:24 AM
  • Hey Ilyas-Ahmad,

    Only the Microsoft team would be able to answer that question as we don't know if or when they'll be pushing an update to the MSI/MSO Excel version.

    Your best bet would be to create a custom function or a set of steps to replicate that functionality as described in my previous reply so you can do that specific filter in the Excel 2016 MSI version.

    Sunday, September 30, 2018 12:24 AM
  • Thank u Moguel for your time and help.

    Tuesday, October 2, 2018 5:27 AM