Forum Discussion

jgni's avatar
jgni
Frequent Visitor
9 years ago

How do I visualize only data which is never than yesterday?

Hi   Newbie here. I don't if it's smartest to do this in the web version or desktop. So I put my question here...   I have an Excel sheet, which contains a date field, and I only want to show the...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi jgni,

 

I'd like to suggest you add a filter at query editor side ("today" as the parameter).

 

For example:

 

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

 

Reference link:

Power Query Current Date Filter

 

 

Regards,
Xioaxin Sheng

jgni's avatar
jgni
Frequent Visitor
9 years ago

I'm still a newbie in PBI.

 

How do I add a filter?

In Excel or Power BI - Query Editor? And if the latter, then how?

 

Regarfs

Jan

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi jgni,

     

    You can take a look at below steps:

     

    1. Open the query editor.

    2. Find out the last steps of your query.

    3. Click on fx button to add steps.

    4. Modify the formula with above steps name.

     

    Full query:

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\sampel.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"End", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [End] = Date.AddDays(DateTime.LocalNow(),-1))
in
    #"Filtered Rows"

     

    Regards,

    Xiaoxin Sheng

    • jgni's avatar
      jgni
      Frequent Visitor
      9 years ago

      Hi

       

      This gives me 4 rows:   #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [End] >= #date(2017, 3, 7))

       

      But this gives me 0: #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [End] = Date.AddDays(DateTime.LocalNow(),-1))

       

      ??

       

      Regards

      Jan

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi jgni,

         

        This gives me 4 rows:   #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [End] >= #date(2017, 3, 7))

        But this gives me 0: #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [End] = Date.AddDays(DateTime.LocalNow(),-1))

        The first one will get the records which date large than yesterday, the second one only get the equal data. If your requirement is filter data like first one, you can try to use below formula to make it dynamic get current date.

         

        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [End] >= Date.From(Date.AddDays(DateTime.LocalNow(),-1)))

         

        Regards,

        Xiaoxin Sheng