Forum Discussion
Filter dates previous Wednesday to Tuesday (Power Query or formula)
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.
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.
Riny_van_Eekelen Thanks again! What you've done is very handy for me to see some of the options with M-code.
The step that is the issue is actually before that when the dates get promoted to header. In the actual spreadsheet the data is being sourced using sharepoint folder location, then the worksheet titles get turned to dates and filtered by last month and current month etc. That's all fine now, the step that's had me hit a wall is this one:Pic on top is how the data comes in, so the column headers are messy. I remove top row, then Promote the Date row to Headers.
But if you look in the OP at the pic of the actual M-code for doing that, it's looking for the specific dates, so ("1/7/22", text) etc for current month. The problem with that being the code is that it then has an error when you go to the next month and then July becomes previous, it's still looking for 1/7/22 and finds 1/8/22. (And for previous month the code is looking for 1/6/22 and finds 1/7/22)
Does that make sense?
=table.TransformColumntypes(#"Promoted Headers", (("Name", type text), ("1/7/2022", type text)I'm not sure on syntax or formula with M-Code, but where it says "1/7/2022" it probably needs to be something that goes: this is a date (not a specific date) just promote it. unsure if I can do something like ("##/##/####", type date) or ("DD/MM/YYYY", type date) and get it to be dynamic, I feel like there'll need to be another step.