cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Select Multiple Tables when data is retrieved from folders

I am trying to run a query for all tables in multiple workbooks that exist in a single folder, but I can only select one table in the list at a time when connecting at the folder-level.

 

I am trying to connect to tables : Cover_Table001 thru Cover_Table009.

 

How do i select each table without selecting the entire sheet?

cover.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Select Multiple Tables when data is retrieved from folders

Sorry, my bad. Didn'T read your thread carefully.

If I understood you correctly, you can do the following:

Start with the table containing the overview of all files in the folder and add a column with this formula:

Excel.Workbook( [Content] ).

This will return the same table that I mentioned in my first post or each row. Expand it and filter the tables you're after.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Select Multiple Tables when data is retrieved from folders

Select one file and delete all automatically generated steps but the first one.

This will return a table with all objects in the Excel-file from which you can select those you need and then expand them all at once.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: Select Multiple Tables when data is retrieved from folders

This method didn't work when I removed steps because i still had the full list of workbooks and not the tables from the file.

 

I am connecting to a folder with 84 files and need to be able to consolidate all of the tables per tab on each file.

Every file has the same tables but there are different tables on each tab in the files.

 

How do i select all individual tables for every file in the folder when i can't select more than one table at a time?

Highlighted
Super User III
Super User III

Re: Select Multiple Tables when data is retrieved from folders

Sorry, my bad. Didn'T read your thread carefully.

If I understood you correctly, you can do the following:

Start with the table containing the overview of all files in the folder and add a column with this formula:

Excel.Workbook( [Content] ).

This will return the same table that I mentioned in my first post or each row. Expand it and filter the tables you're after.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Helper III
Helper III

Re: Select Multiple Tables when data is retrieved from folders

This is a step in the right direction!

I just need to combine those tables now that all of the other tables have been filtered from each file so that the contents of the tables are diplayed through one query instead of seperate queries for each table.

Do you have any insight into this action?

I am trying to consolidate the 9 cover tables in the image below so that they are all able to be pulled and refreshed with 1 query.

a1.jpg

Highlighted
Super User III
Super User III

Re: Select Multiple Tables when data is retrieved from folders

Not sure I understand you correctly.

By now, you should just have one from-folder-query, right?

After you've expanded and filtered the new column, you should see all (the same) tables from different files in one table. Can't you just expand them?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: Select Multiple Tables when data is retrieved from folders

You did everything right, I just missed the expand row values step. 

Thank You Very Much! You have been tremendously helpful!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Kudoed Authors