Some Rows Not Accessible/Viewable When Filtering by Date/Time Column

[somecallmedave]

Hello,

One of our database tables has a date/time column that has dates going back to at least 2018 that can be seen in the Power Query Editor window (Figure 1).

We noticed that data in rows for 2018 do not appear in visuals, as can be seen in a table visualization of that column (Figure 2).

When troubleshooting in the Power Query Editor window, I tried sorting the dates in ascending order and it will not display entries before 03/01/2019 (Figure 3). Also when filtering rows by this date/time column for entries before 03/01/2019, it returns an empty table.

Any idea why that data is inaccessible even though it can be seen in the Power Query Editor window?

 Figure 1

Figure 2

Figure 3

[BeatrixKiddoBM · Accepted solution]

It is possible that some of the data in your table has been archived and your preview is showing an out of date snapshot of the data. Try refreshing the data connection and preview and seeing if the dates that can't be sliced or filtered are still there. 

Cheers!

[somecallmedave]

This seems to be exactly the issue! The old data was not there in preview this morning.

[v-lili6-msft]

hi  @somecallmedave 

If you could see 2018 data in data view in power bi desktop.

If possible, could you please share your sample pbix file for us to have a test, that will be a great help.

Regards,

Lin

[TomMartens]

Hey @somecallmedave ,

I understand that you see data from 2018 in Power Query,

You do not see data from 2018 in the Report view if you add the date column to any slicer, after you have refreshed the data.

Check the following:

1. Is there a step in the PowerQuery query that filters out dates from 2018
2. Do you see dates from 2018 in the data view, after you have refreshed the data
3. Are there page level filters that prevent the use of 2018 data
4. Make sure there are no hidden slicers on the page, sometimes this happens if one is experimenting with slicer synchronization and hidden slicers. Use the selection view to make sure there is no hidden slicer
   

Hopefully, this provides some new ideas

Tom