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

[jgni]

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 rows, if they're end date in 'End' are after yesterday.

How to?

Sprint	Dev. board-møde	Kick Off	Start	End	Kommentar
28	14 nov	25 nov	15 dec	18 dec	Inklusiv juleferie
29	05 dec	09 dec	19 jan	08 feb
30	19 dec	22 dec	09 feb	08 mar	Inklusiv vinterferie
31	30 jan	03 feb	09 mar	29 mar
32	20 feb	28 feb	30 mar	19 apr	Inklusiv påskeferie

[v-shex-msft]

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]

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

[v-shex-msft]

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]

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

[v-shex-msft]

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

[jgni]

Thank you! I'll try that.....