Hi - I am trying to create a power query that links a production schedule to an ordering guide, so the guide can read the schedule and adjust quantities on hand. I had this set up and was working beautifully, but now the workbook that contains the schedule has changed format and it no longer is found on one sheet within the workbook, but there is a new sheet for each week. There are no tables in any of the sheets, but all sheets are formatted to look identical to one another. Each week, a new sheet is created with the next week. So the query needs to be able to find this new sheet each time and update the data found on the new tab. Preferably, I'd like the data from the schedule to all load into one table in the order guide. I've attached the schedule file that I'm working with. Any help or guidance that you could provide, would be greatly appreciated!
Thank you,
Amanda
Nothing attached. Please try again.
My apologies! I've reattached the file now.
When you connect to the file, you will see the content of the entire workbook. All visible tabs in alphabetical order with a checkbox in front of it. Don't select any single one, but click the 'header bar' with the workbook name. I.e. directly above the first item. Now press Transform.
This will give you a detailed list of everything PQ finds in the workbook. All tabs (visible and invisible) and some hidden objects. From here you can filer for 'Name' beginning with "Wk of". Then, extract the week numbers in a separate column and split that one into the month number and day number. Now Sort the table by month (desc) and then by day (desc) and keep the top row. That selects the tab for you the most recent week in the workbook.
The PQ code would look like this:
Thank you very much for your help and response, Riny!
I will be implementing this in my workbook tomorrow and will let you know how it goes.
