Forum Discussion
Filter dates previous Wednesday to Tuesday (Power Query or formula)
EDIT: Have updated this as I've worked out some issues but discovered more....
I am trying to use Power Query to combine some monthly data into weekly chunks provided by 5 different offices.
The specific data isn't too tricky, but it's divided up in a way that makes it hard to get the days I want in all circumstances.
Each office has a report split into Monthly worksheets (Jan-22, Feb-22 etc)
Individual Days/Dates end up as the Column Headers.
I have been able to get PQ to sort down to the current Month worksheet, and then show dates for previous week.
However, I'm struggling to work out how to cover for end of month, where, for instance, that current month will be July, but I want to get the data for the last week of June.
(Have been able to sort out getting previous month to show when you're in the next month.)
I haven't confirmed yet, but I believe the report might need to be Wednesday to Tuesday, not Sunday to Saturday. (This issue is now secondary to a problem with using the dates as column headers.)
If I promote the dates to be the column header, as per this pic, then the formula is looking the exact value of the current month it is, as soon as you go to the next month, you get errors, because it can't find 1/7/22, for instance, and instead finds 1/8/22.
I know very little of M code, I assume there is a formula that could get it to look for dynamic data in column names?
Have attached an updated demo version of the basic data.
7 Replies
davidmaddock54 The workbook you attached contains a sheet for January with nothing in it and one for February with some data in it. You mention that you were "able to get PQ to sort down to the current Month worksheet", but you don't include the query(s) nor the results of it. And you don't show what the expected outcome should be if it's around the end of the month.
Please upload a file that give a more complete picture of what you are dealing with.
- I have updated the OP and the attachment. After further work, my issue currently lies with the formula used to promote first column to headers. Hopefully it all makes more sense now.
davidmaddock54 Thanks! I see the queries but not much is going on. They connect to tables. You unpivot and append two of them (Previous and Current)into one. Is it that you want to connect to all data and then do some analysis based on what's in the current month vs. the previous month? And the issue then may be that when you do work on say June and July in the beginning of August, the current month should still be July and the previous would be June. Please clarify. In the meantime, have a look at the attached file, where I connected to all tables in the sheet and dynamically selected what is Previous and what is Current. Perhaps something to get you started, though it does require some M-code manipulation.
- Oops. Something didn't save. Thanks Riny. Will have a look and update.
