Excel Forum

Importing Multiple Tables from Multiple Excel Files

MohamedGadAllah

EGYPT - Cairo

Member

Members

Forum Posts: 40

Member Since:
December 13, 2021

Offline

March 1, 2023 - 8:51 pm

Hi,

I have a folder, that contains identical files, and each file has identical tables.

I was watching this tutorial in detail, and thanks a lot for clearing the technique.

Import Multiple Files Containing Multiple Sheets with Power Query

Please, I've followed the tutorial, but can not achieve the end desired result.

I want to combine each table with its alike tables from other workbooks.

This is the video for the steps I've followed:

https://youtu.be/F9W1-OobQNk

The last step as shown in my video does not show the data from other files, so how to show or include it?

Thanks

-later edit-

* .zip file includes all .xlsx files.

* .pbix file includes the steps shown in the video.

* I posted asking about this issue earlier here, and I was not able to figure out the detailed reply. (I am adding this note respecting your valuable time as you are a very helpful website indeed, and I replied over there several times but I was really not quite sure understanding, so I thought to ask about it here).

See Answer

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Moderators

Forum Posts: 1109

Member Since:
January 31, 2022

Offline

March 1, 2023 - 9:57 pm

I created 12 separate queries like you, and they all pick-up the data from each of the files. When I looked at your video I notice that you worked off the "Binary" column where Mynda expanded the tables from the added column with the content. I did the same.

Then I also added a Grouping step that captures all the same tables from each of the files in a group. Created separate queries from each group containing three tables. Now you can expand these tables for every group. I did that for "tbl_Horizontal_Income_Statement" only.

See if you can follow the steps in the attached file, and let me know if it does what you need.

Answers Post

MohamedGadAllah

EGYPT - Cairo

Member

Members

Forum Posts: 40

Member Since:
December 13, 2021

Offline

March 13, 2023 - 7:35 pm

@Riny van Eekelen

First of all, I am sorry for the late reply I had some issues that need more attention (related to health), so please accept my apologies.

Thanks a lot for your reply.

You solved it the way I needed it.

I learned a lot from your reply.

So, please, if you may open the power query editor (sample-1_v2.pbix) during reading my current words that would be awesome.

Please, there is an issue that I do not follow from your sample file, the issue is that I've recreated the whole file and used the sample file v2 you thankfully created as a map for me.

For the query titled OriginalFolderContents, I've followed the exact steps as yours, and the steps stopped at this step Removed Other Columns1, like this image:

And according to the image, the arrangement of the table columns ended with the DATA column, while my table ended with the NAME column, like in this image: