Combine Multiple Tabs & Multiple WorkBooks Through Power Query

[snaplink22]

Hello,

I've been struggling with getting Power Query to combine 2 workbooks, with 2 tabs each. Here is my below setup:

1. Both workbooks are located in the same file folder on my desktop and are both .xlsx files.
2. Each tab's data has been converted to a table, with each table being named with the same prefix (WASS_) and the tabs titles follow the same naming convention. Each table and each tab has a different ending to their respective names however (e.g. WASS_Lindrick or WASS_Pasco)

3. On a new workbook, I select Data, Get Data, From File, From Folder. I select browse and then select the folder where these workbooks are stored.

4. Here's where things get a bit murky. I've seen a variety of videos and articles that reference what I'm supposed to do next, with each one being different. The options I have available are:
Combine drop down to select: "Combine & Transform, Combine & Load, and Combine and Load To"
Load drop down to select: "Load and Load To"
"Transform Data"

5. I've experimented with all of them, but the consequences seems to be "Transform Data". Which in turns give me this menu:

6. I removed all columns except the first two and then click on the double arrows under the Content header and select the table WASS_Lindrick1:

This, however, is where it deviates from all the videos as I only see 1 tabs worth of data displayed after clicking OK. Every thing that I've researched indicates I should be seeing all the data from all 4 of my tabs now, but I'm only seeing the first tabs data.

Any assistance would be greatly appreciated!

 

[snaplink22]

So can I mate and you posted "just expand Content (Binary) don't Combine". That process combines and you stated not to do that, so which is it?

 

[GraH]

just expand Content (Binary) don't Combine

True, that's also working on Excel files these days. Tend to forget about that, sticking to old habits.

I receive the following error: [DataSource.Error] The process cannot access the file (details out file location) because it is being used by another process.

Simple solution is closing these files. Or is there a very particular reason why they should be open when you are refreshing?

So I selected WASS_Lindrick1 previously and clicked OK and that wouldn't allow me to refresh the data if one of those files was open. From my understanding, the above screen combines all the tabs/books.

That screen offers to select a "sample" from which it creates a function. That function is then applied on the other workbooks. It can be handy but generally speaking I don't like that.

 

[sandy666]

click Expand, you'll see new window then click ok

don't use = Excel.Workbook([Content])

doesn't matter your files are open or not - it should work after save them

 

[snaplink22]

These files are open throughout the day and are populated by other departments, unfortunately. I'd would prefer to be able to refresh throughout the day to pull the data and we sometimes run into the challenge where these people don't close out of the file before they leave.

 

[snaplink22]

Doing that only pulls 1 tab of data in though. How do I go about adding in the other 3 tabs worth of data?

 

[sandy666]

to whom are you addressing your posts?

 

[snaplink22]

to whom are you addressing your posts?

Sorry, this one is for you:
Doing that only pulls 1 tab of data in though. How do I go about adding in the other 3 tabs worth of data?

 

[sandy666]

I'm not able to Refresh my query while the other books are open

post#2 but save source files before Refresh

 

[snaplink22]

post#2 but save source files before Refresh

I do, but if the source file(s) are still open it gives me that error.

 

[sandy666]

what is your excel version? update profile about that and OS

create representative example, zip it than share via onedrive, googledrive, dropbox or any similar and post link here