Pulling in data from multiple tabs but all with the same range

[Anonymous]

Hi there,

I am unsure of the best way to transform this data into one main dataset.

We have our crew_plan spreadsheet that is released each week.  It has multiple tabs for each production and at the bottom of each tab there is an array.  The array is always the same J600:J710 lists the crafts for the artists (Animation, Art, FX, CharFX etc etc). K600:JI710 contain a total count for the artists on a week by week basis out til the end of 2024, so each column is a week.

Here are my challenges.  Each tab does NOT contain the production name, it is only displayed on the tab name in Excel and the site of that production is not on the tab also, i.e. Vancouver or Sydney.  The tabs are grouped with a tab that says Van From.. then the production tabs and a blank tab called Van To, Same with Sydney and LA.  So for each production I would need to load in the data from the various productions tabs and then add the site and the production name for filtering.

Below is what I am working with in the array, per the below the first two columns are what I am going to need to add to the table based on the name of the tab and the site where the production is, the other columns will come from the Array. 
The columns in blue do not exist and what I need to add to each array / build into the power bi transform when the information is pulled in.

I am pretty stuck with how to achieve this.

Many thanks.

[AlexisOlson]

You can load multiple sheets in the same query since when you connect to an Excel file, it gives you a list of items.

I'm attaching a simplified sample Excel that looks like this:

When you connect to it in the query editor, the first step looks like this:

You can apply a sequence of steps to pull out the parts you want. Here's a quick and dirty example:

let
    Source = Excel.Workbook(File.Contents("C:\Users\aolson\Downloads\crew_plan.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column8", "Column9", "Column10"}, {"Column8", "Column9", "Column10"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each ([Column8] <> "Artists" and [Column8] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Column8", "Artists"}, {"Column9", "Date"}, {"Column10", "Total"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Name", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"City", "Project"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"City", type text}, {"Project", type text}, {"Artists", type text}, {"Date", type date}, {"Total", Int64.Type}})
in
    #"Changed Type"

To try this on your own machine, you'll need to download the attached file and change the file path in the query above to point to it properly.

The result looks like this:

The exact steps will obviously differ for your case but hopefully the principle is clear enough.

[Anonymous]

@AlexisOlson  sorry Alexis I have wasted your time with this. I mucked up the post I put, the edited post shows the data as it looks in the Array, the image shows how the data looks in the arrays and the first two columns is what I need to populate per array as it comes into Power BI.

[Anonymous]

Also the Location isn't on the tab name of the production.
There is a Tab with no content that just says Van From.. then the next 2 tabs are the vancouver projects then there is a tab with no content just named Van To, which means the tabs between those buffer tabs are for Vancouver, the same is for Sydney.

[AlexisOlson]

I'm not following. I think you'll need to share a file or at the very least screenshots of the spreadsheet layout (not just a specific range but something that shows how it's all related).

[Anonymous]

OK here is a shot of the tabs (A mock up to hide actual production names).

The tabs in green and red contain nothing, they are just a buffer that shows which are the Vancouver producitons and which are the Sydney productions.  The project tabs are the ones that contain the arrays and crew information.

[AlexisOlson]

Oh wow, I don't think I ever would have gotten there without the screenshot. I've never seen tab names used like this.

I think I understand now though and if you set up some dummy data in this file and link to the .xlsx, then I'll give it a shot.

[Anonymous]

Eergghh  I just realised that in the array there are totals at different points in the data..  per below as a sample, the lines I highlighted in yellow are actually totals from the related lines below 😕

This is getting more and more problematic?!

Also, how do you attach a file to the post?

[AlexisOlson]

Post a link to the file uploaded to the cloud via DropBox/Google Drive/OneDrive/SharePoint or whatever service you prefer.