How to select more than one table from a file when importing identical files from a folder

Hi,
I’ve a folder contains identical files.
Each file includes 7 tables.
Each table has different columns except one single column exist in every table, that column has a header titled company.
So how to import all excel files from that folder, and when importing the files, I need to include the 7 tables from each file so I can create and manage relationships in between them.
Thanks




image

So, now I’ve 11 tables, I want to select 7 of them to transform data as needed.
Why I can only select one single item?

Hi @MGadAllah,

You can only select one item because you have choosen from Folder/Transform & Combine.
This is useful for files that are structured the same way and contain similar data. From what you described I’m not sure that fits your requirement…

.
Here’s are some initial questions for you.

  • Does each table match/map to another table in any of the another files? Let’s say each file contains a table called “A”, do you want to combine the data in those tables?
  • If so does their structure- and column names match?
  • Do they share the same (Excel table) name?
    .

But it would be even better if you’d provide some mock up dummy files, that match your scenario. Each file containing 7 or … tables with and a separate expected result file.
That is the most effective and efficient way to ask and provide community support…

1 Like

Hi @Melissa
Thanks a lot for your valuable time replying my thread.
I’ve the following assumptions:

  1. I’ve a folder contains massive number of files.
  2. All files have an identical structure.
  3. Each file includes 12 identical tables with identical headers.
  4. Only the data inside the tables that will be changed, but not the headers at all, so sometimes the tables include more or less rows of course.
  5. Of course, I will add new column for a new year when the new year comes, but no column will be deleted from the old years.
  6. So what I did so far was to place all files in one single folder, and when I tried to import them all from a folder I was only able to select one single table and not the whole tables.
  7. The need for the tables is that it includes formulas, so I just fill in the data entry cells, then everything being calculated automatically.
  8. I hope to find a scenario to just keep all files in one folder and just get everything updated when I add new files to the folder.
  9. Also, I hope if there is a way to do all required customization in power query for the power bi.
  10. Also when I tried to make a relation between tables I’ve got an error that tables has many to many relationship, while only one column is common between them all which is the Company column.

I hope that you have enough information now.
Sample_File_English_EnterpriseDNA.xlsx (40.0 KB)

Hi @MGadAllah,

A many to many relationship is created when there is duplication of values on both sides AND/OR it contains null/blanks.
.
.

You didn’t provide an expected outcome, so you will have to take it from here…

This is what I did. Created a folder and placed 3 copies of your file in there.
image

Added a FolderPath parameter, select “manage parameter” add your folder location to the list (1) and set it as current value (2).

Created lists for each “set of tables” that are to be combined from each file. Adjust to your needs.

And the final staging query, gets the files from the folder.

image

Just for illustration, here you see the “content” of the first “Binary”
Because I have 3 copies of your file in my folder each binary returns this table.

Now we know what the binary contains we can use this to combine multiple tables from each file before combining the data from all files.

Started each result query, by referencing the “FolderContents” staging query and selecting the “Contents” column. Using the content information filtered each of the tables (1):
([Kind] = “Table”) and List.Contains( VerticalGroup, [Name], Comparer.OrdinalIgnoreCase )

From the result, extracted the content in the “Data” column (2) as a list
I noticed there were errors in the file, I removed those (3), adjust to your needs. Tip. Avoid bringing in worksheet function errors because they also return an error in PQ. And Combined these tables.

Finally combined all returned tables (4) as well.

Here’s a sample.
Combine multiple tables from Files from Folder.pbix (13.3 KB)

I hope this is helpful.

2 Likes

Dear @Melissa

I much appreciated your valuable time to do what you did, as I know expert members here at eDNA are extremely busy, and your time and experience are worth a lot indeed.

With all honesty, and I am not embarrassed to say it in a short sentence …

What you did is far above my head, and the way you presented the contents is more logical and better than what I was even thinking about, so thanks once again for inspiring me.

I am not saying that you did not say it clearly, but I am saying that many steps I was not able to figure out how these steps were done because of my little experience with Power Query and PowerBI.

I am sorry, but what is the right outcome that I had to attach earlier?
Do you mean the Excel files?
Sample_File3.xlsx (40.0 KB)
Sample_File2.xlsx (40.0 KB)
Sample_File_English_EnterpriseDNA.xlsx (40.0 KB)

After opening the .pbix you thankfully attached I was not able to find any way to reach the window of managing the parameters, and in turn, I was not able to even load the files within it.

So I followed the steps you thankfully mentioned as follow:

I’ve created a folder, and added the 3 sample files to it like this:

image

I’ve edited the company name in each file, so:
File 1 has Company A.
File 2 has Company B.
File 3 has Company C.

Then I finally added the parameter for getting data source (but still do not know how to find the window or the option for managing the existing parameters)

I followed these steps:

image

Please, how you did do it?

In the last image above, I’ve tried to click on all options:
Combine.
Combine, and Transform Data.
Combine, and Load.
Load.
Transform Data.

But each option does not give me what I see in your reply, so I guess there is a step in between in the middle.

I can see the name of the folder you grouped items under it, I mean the StagingQueries[6], but above it, there is an item titled Queries [10], so are these extra 4 queries something related to the middle steps?

I hope you may have a few minutes to reply to me or if I may ask and if your time allows that you may record your steps in a short video (I am sorry if my request is not an appropriate one) as I’ve spent several hours so far and can not figure it out.

Thanks a lot once again.

Hi @MGadAllah,

Select a Parameter query in the query pane and you’ll find the option to manage it here.
image

.

I created them manually. You can learn more about that here or in the Fundamentals course within the learning portal

.

You can genereate the “FolderContents” staging query by selecting => Transform Data

That is the query I referenced to build the 4 result queries. The syntax you find in the GetData step was written by hand, not through the UI. I’ve described key parts and their function in my previous post.

1 Like

Thank you so much for that detailed answer @Melissa

We hope this helped you @MGadAllah :slight_smile:

If not, how far did you get, and what kind of help do you need further?

If yes, kindly mark as the answer that solved your query.

1 Like

Thanks a lot, @EnterpriseDNA for following up with me, and thanks a lot @Melissa for your help.
Honestly and I was embarrassed to post that I was not able to proceed or continue the solution provided by @Melissa because I was not able to figure out the solution of using the list and doing the integration later on.
This is because I am still learning Power Query and Power BI.

Hi Mohamed,
nothing to be embarrassed for if you do not understand an issue. => follow up and ask
I hope you can still clarify your questions with @Melissa! You seem to be close.

If that remains somehow too much of a hurdle, I have a no questions asked solution where you basically have to add your folder path and it should evaluate the 12 Tables from each file of that folder for you.


If you can, you can of course also use the suggested Folder Parameter.
:warning: The solution works if all files have 12 tables!
Why 12? I took all, because I didn’t know which ones you wanted.
[It would work with more and also less tables but then you would need to adjust.]
12 tables I/you can still handle manually. Create one and then copy.
image

Then I reselected the line referencing to the Downloads query and renamed the query.
image
I worked from 0 to 11, and then resorted a bit manually, e.g. brought the ratios to the end as you probably do not want to have them in the report. Resort according to your needs, and feel free to delete the ones which you do not need.
[I do not know how to automate the query creation and renaming in PowerBI, that is why this is manual work and you are “not allowed” to change the number and/or name of the tables! In Excel you could automate the query creation / naming with VBA.]

“5. Of course, I will add new column for a new year when the new year comes, but no column will be deleted from the old years.”
:warning: Because of this the expansion step needed to be customized.
= Table.ExpandTableColumn(Custom1, “Data”, Table.ColumnNames(Table.Combine(Custom1[Data])) )
=> So this turnkey solution has you covered also in the next year. :heavy_check_mark:

  1. You can keep all files in one folder and get everything updated when you add new files to the folder.
  2. The tables are taken as they are, so you might also want to add additional transformation steps e.g. filter out total rows or whatever you need.
  3. All 12 tables are related to a table “Company”.

The errors in the tables mentioned by @Melissa you best address in the source files!

I wanted to understand your problem and looked at the files. What I did was a very manual approach, but it might be that my explanations are still difficult to understand.
=> That should not matter, it’s plug and play.
select more then one table from file when importing identical files from folder.pbix (170.8 KB)

Regards,
Matthias

2 Likes

@Melissa
@Matthias
Thanks a lot and much appreciated your helpful replies.
Both of your replies used the List tool in power query and this is the exact part where I do not understand.
I watched a lot of videos about using a list in power query but still do not understand it correctly.
I know the step where you select a column, then click on convert to list.
But how to make use of the newly created list? this is the part where I am stuck.
Most videos on YouTube include codes for adding or using lists with { }, and that makes me feel that there is a starting point for adding or using a list that I am still missing or do not understand.
For example, I know that I add a table and then expand its contents and then do what I need, then I apply and close.
Middle steps for adding lists and then using lists in your query, I am still struggling with.
I followed your advice @Melissa and read the document you mentioned and also did a search for your videos here in EnterpriseDNA, and on YouTube and watch a lot indeed and I am not going so far when I can confirm that you are such a genius and a role model when it comes to data analysis and building models.
This is the part that I am stuck with.
I know it is not my right to ask for it, but if your time allows making a video of your steps would be a huge favor.
Thanks a lot for your valuable time helping me.

@MGadAllah,

It be my pleasure to create a video on this topic, I’m sure it will be helpful to others as well in future. :white_check_mark: Consider that done.

In the mean time - if your schedule allows it, consider working through the Fundamentals course in the portal. I’m in the process of finalizing a new course that makes a good follow to that because it deals with structured values (lists, records and tables). That should become available very soon…

And please be so kind to mark this thread solved, I will post a link to the YT video here once that becomes available on the eDNA Channel. Thanks!

1 Like

@Melissa @Matthias
Oh!, Your solutions both are brilliant and amazing indeed.
I am really sorry for not ticking the thread as solved previously, as you both provided solutions, but it was me who did not understand it and it is from my side and not your side at all, so please accept my sincere apologies.
Thanks a lot for your valuable time helping me.
M!

No worries at all @MGadAllah
We all need time to learn, @Matthias and I just have a head start… :blush:

1 Like

Hi,

Please, if I may ask about something related to that issue I do not understand why I am getting an error.
I’ve followed the document here:

How to combine multiple tables from files in a folder PDF.pdf (523.6 KB)

And these are the files I am working on to apply

Company A.xlsx (41.3 KB)
Company B.xlsx (41.3 KB)
Company C.xlsx (41.2 KB)

This is a video for the steps I’ve followed:


(Please view in x2 speed, as I’ve found it a bit long, sorry!)

I do not know why I am getting an error for the last step on the video.

Thanks

Hi @MGadAllah,

You don’t have parenthesis for the Excel.Workbook function, it should look like this:

Excel.Workbook( [Content] )

Also I think, your intention isn’t to combine all tables from all files BUT combine a selection like virticals, horizontals, ratios for example. Therefore you’ll need to amend your filter and will need to repeat the entire proces for each combination you want from your files…

1 Like

Hi @Melissa

Thanks a lot for the fast reply.

Also much appreciate your valuable time preparing that document.

  1. For the 1st part of your reply, you were right regarding the (), {}, and [ ]. Much appreciated, I’ve added the appropriate spaces and () with [ ] and it did not report any error.

  2. For the 2nd part, I am sorry for not clearing my point correctly in a right and straightforward way from the early beginning for you or for @Matthias, I do want to combine each table with its associates’ alike tables from all other workbooks
    So for our current example, I’ve 12 tables in each workbook, and the 1st table is titled “tbl_Balance_Sheet_Statement” and I want to combine it with all other similar tables with the same title from other workbooks.
    The end result will be 12 big tables from all workbooks.
    Then I want to make a query for each table to be able to unpivot its columns (Is it the right approach for this case? or would you suggest a better one?).
    Then load everything to Power BI.

Thanks

@MGadAllah, I thought so too. In that case - as described in my previous response - you will need to add additional logic to the filter step. The purpose of that step is to identify all tables that need to be combined.

Once you have completed this proces once, you can duplicate your query, change the filter condition to identify a new set of tables to be combined and build from there… best of luck.

Hi,
Thanks a lot, dear @Melissa for your reply.
Please, I’ve followed the steps, but I feel very stupid donkey not being able to finish the task :joy:


Now, I’ve 2 questions (I know you must be angry at my stupidity :joy: as if I surrender and quit I will not learn), but:

  1. At which step in the last video do I must add the list that contains the 12 tables, given that I want each table alone and not combined with any other table in the same workbook?
  2. At the last step of the video the resulting query only contains the contents of the 1st file and not the rest of the files for the same table name, so how do combine it?
    These are the files I’ve used:
    Company A.xlsx (41.3 KB)
    Company B.xlsx (41.3 KB)
    Company C.xlsx (41.2 KB)
    sample.pbix (157.4 KB)

Much appreciated and sorry once again.

Hi @MGadAllah,

You are doing fine, you’re trying to work through it on your own - that’s great. Learning takes time. See if this short video is helpful to you.

GetTables

Right click video to View in new Window

1 Like