Forum Discussion
Power Query Date Filter missing dates
I am in charge of tracking a covid check-in database for my org, and I have a spreadsheet that has been working for weeks. It's now failing on 2 fronts, and I can't see why at all.
- The check-ins are done using MS forms.
- Unfortunately, the forms weren't set up from onedrive, so I have to DL them every time.
- To ease this process, I am sourcing the PQ from folders on my desktop. The first issue is that these have just stopped refreshing, even with new checkins added to the spreadsheets put in the relevant folders. Was working great for 3-4 weeks, and now won't update.
- This may be related, but I can't see why: the further steps I have added involve filtering date, as management want to see weekly lists. I set up the date filter to show only previous week. (This was working and updating. But is now not.)
- This may also be a cause, but I can't see why, except that it correlates with when stuff started to not work: As forms were set up for specific sites, and I needed to aggregate, I made a column with site name from the file name.
Basically, I have dates in the data that are in the last week, and they still don't show. I have tried changing the data type of the date column, date only, date and time, and it doesn't seem to matter.
Any advice would be appreciated.
4 Replies
- The problem is MS forms does not have data validation so anyone can enter text with leading or trailing spaces, check for that using SUBSTITUTE and TRIM Functions
If refresh queries one by one in Power Query editor do you see any errors with that?
- No. Haven't seen errors. I may check that specifically, I just did a test new sheet which has worked.
I am not sure on the reason, but it seems it doesn't like not finding any data.
So if I do the date filter on the initial PQ, it seems to mess things up, but if I do the PQ from folder and just dump it into the spreadsheet then PQ that with the filter it all works.
