Hi Excel Freaks 
I was asked to prepare a solution to quickly combine data from different files (located in this same folder). All files have a similar name (advantage for me), they all have this same tabs (Tab1, Tab2, Tab3, etc. - another advantage) with tables (Table1, Table2 - great again). My task is to create an automated excel, which gathers data from all files from all tables in one big data set, which is later filtered on one column ("Has error" - this is an easy one). I can easily create a PQ to gather info from one file or all files from one tab (i.e. Tab1), but how to combine those two options? In PQ when I do "combine files" option, in the sample file I have "= Source{[Item="MyTable2",Kind="Table"]}[Data]" meaning, that at one time I can get data from all files but from a specific Table/Tab. Another problem is that I don't know how many files and how many tabs will be in the folder, it might be 3 files with 5 tabs each, it might be 20 files with no. of tabs from 1 to 10 -> every month it will be a different data set.
I was asked to prepare a solution to quickly combine data from different files (located in this same folder). All files have a similar name (advantage for me), they all have this same tabs (Tab1, Tab2, Tab3, etc. - another advantage) with tables (Table1, Table2 - great again). My task is to create an automated excel, which gathers data from all files from all tables in one big data set, which is later filtered on one column ("Has error" - this is an easy one). I can easily create a PQ to gather info from one file or all files from one tab (i.e. Tab1), but how to combine those two options? In PQ when I do "combine files" option, in the sample file I have "= Source{[Item="MyTable2",Kind="Table"]}[Data]" meaning, that at one time I can get data from all files but from a specific Table/Tab. Another problem is that I don't know how many files and how many tabs will be in the folder, it might be 3 files with 5 tabs each, it might be 20 files with no. of tabs from 1 to 10 -> every month it will be a different data set.
