Excel Forum

Help with Query that pulls data from tabs in a workbook

Amanda Beyer

Member

Members

Forum Posts: 14

Member Since:
September 1, 2021

Offline

July 10, 2024 - 4:41 am

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

Alan Sidman

Steamboat Springs, CO

Member

Members

Trusted Members

Forum Posts: 204

Member Since:
October 18, 2018

Offline

July 10, 2024 - 6:29 am

Nothing attached. Please try again.

Amanda Beyer

Member

Members

Forum Posts: 14

Member Since:
September 1, 2021

Offline

July 10, 2024 - 7:02 am

My apologies! I've reattached the file now.

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Moderators

Forum Posts: 1021

Member Since:
January 31, 2022

Offline

July 10, 2024 - 3:51 pm

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:

let

Source = Excel.Workbook(File.Contents("/_________/Production-Schedule-2.0-1.xlsx"), null, true),

#"Filtered rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Wk of")),

#"Inserted text after delimiter" = Table.AddColumn(#"Filtered rows", "Text after delimiter", each Text.AfterDelimiter([Name], "of ", 0), type text),

#"Split column by delimiter" = Table.SplitColumn(#"Inserted text after delimiter", "Text after delimiter", Splitter.SplitTextByDelimiter("."), {"Text after delimiter.1", "Text after delimiter.2"}),

#"Changed column type" = Table.TransformColumnTypes(#"Split column by delimiter", {{"Text after delimiter.1", Int64.Type}, {"Text after delimiter.2", Int64.Type}}),

#"Sorted rows" = Table.Sort(#"Changed column type", {{"Text after delimiter.1", Order.Descending}, {"Text after delimiter.2", Order.Descending}}),

#"Kept top rows" = Table.FirstN(#"Sorted rows", 1)

#"Kept top rows"

From here you can expand the Data column and continue your journey.

If, on the other hand, you can influence the naming of the workbook tab names for each week, make sure that you always use two digits for the month and day numbers. So, rather than 'wk of 8.4', call it 'Wk of 08.04'. Then, the most recent week will always sort last and you can just keep the bottom row after the initial filter step.

Amanda Beyer

Member

Members

Forum Posts: 14

Member Since:
September 1, 2021

Offline

July 11, 2024 - 6:56 am

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.