Filter by Today’s date in Power Query

[RaphaelAlves]

I need to filter by today date. There is a colunm with some dates where I’d like to filter only the today’s date. How to do it? Thanks!

[Nathaniel_C · Accepted solution]

Hi @RaphaelAlves ,
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

= Table.SelectRows(#"Name of Previous Step", each [Column1] = DateTime.LocalNow())

Technically it returns a Date/Time value, where TODAY() returns a Date value. The literal closest to TODAY() is Date.From(DateTime.LocalNow()) which you should use rather than DateTime.LocalNow() alone if the column you're filtering is already a plain date type rather than date/time.

[Syndicate_Admin]

I was trying to do something similar and the logic or syntax may apply.  I was trying to limit my TCdate (timecard date) to any date prior to today.  I used this in Power Query and it worked:  = Table.SelectRows(#"Filtered Rows", each [TCdate] < List.Max(#"Filtered Rows"[TCdate]))

[Nathaniel_C]

Hi @RaphaelAlves ,
Just click on the top of the column on the down arrow, your filter selection will come up.  Choose there.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[RaphaelAlves]

Hi Nathaniel,

It does not works... I’m connecting to an ODS table, so this colunm is filled out automatically automatically with a new date. I need to filter to show only the current date.

[Nathaniel_C]

Hi @RaphaelAlves ,

Tell me what an ODS table is? It sounds like you are trying to filter before it gets to Power Query. Is that right? If so I cannot help you. If that is the case you would be better off to post again, and describe that.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[RaphaelAlves]

I need to do the filter using the power Query. Forget about the OSD, it’s just a table where the data comes from. The Column with dates are filled daily with new dates. I just need to filter to show only the today’s date.
Something lile that: [DateColunm]=today()

[RaphaelAlves]

Hi
I could configure it using DAX in power BI, but my table is too big making it slow, so my intention is to filter it before applying to power BI. Let me know if you may help me, thanks mate!

[Nathaniel_C · Accepted solution]

Hi @RaphaelAlves ,
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

= Table.SelectRows(#"Name of Previous Step", each [Column1] = DateTime.LocalNow())

Technically it returns a Date/Time value, where TODAY() returns a Date value. The literal closest to TODAY() is Date.From(DateTime.LocalNow()) which you should use rather than DateTime.LocalNow() alone if the column you're filtering is already a plain date type rather than date/time.

[RaphaelAlves]

Thanks, I will try soon this solution and let you know if it’s ok.

[v-frfei-msft · Accepted solution]

Hi @RaphaelAlves ,

If your data is date type not date/time one, Then we should use DateTime.Date(DateTime.LocalNow()) to work on it.

Btw, M code for your reference.

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

[Nathaniel_C]

And I just say that because of how the forum works.

[MarcUrdang]

Hi Nathaniel ... I am not sure if this is the right thread for my questoin .. .but perhaps you can help? When I get data into Power BI I see that the date created date is based on some other time zone ... in my case about 9 hours behind .. which means when 'getting data' I can't use the Date Created filter as the times are not correct. 

Can you tell me how to set this to my particular time zone?

thanks

Marc

[Nathaniel_C]

Hey @MarcUrdang ,
If I understand your question, here is a good video that will take you through the steps Ruth at Curbal 
Thank you,

Let me know if you have any questions.