Charlie987
New Member
- Joined
- Jul 25, 2020
- Messages
- 26
- Office Version
- 365
- Platform
- Windows
Hello all,
I am hoping to find a solution for the following ( I am very new to Power Query so limited with knowing what is and isn't possible.)
I have multiple workbooks that represent a snapshot of a business overtime.
eg. Data Workbook Jan 24, Data Workbook March 24 etc etc etc.
The workbooks can be created at any point in time and also can represent any point and span of time, so any solution won't be able to rely on fixed dates. Eg. the Jan 24 workbook could have data tables from Nov 23 - Jun 24 - not necessarily starting Jan 24.
The workbooks can all be saved in the same folder. The workbooks will contain common tabs eg. CFB, A&L, Summary (there will be more, but limiting for this example). The tabs will contain tables of data. I want PowerQuery be able to "find" all of the tabs as new workbooks are created and add these to PowerQuery within A SEPARATE file called "Analysis Workbook". The intention is to be able to combine the data and analyse changes overtime. The solution will have to be dynamic enough to be adopted for analysing a different business (different set of workbooks) but each instance of analysis workbook will only be applied to the one folder/set of workbooks. So it won't be able to be pre-determined how many workbooks there will be in the folder, when they will be dated or when new workbooks will be created.
Using CFB as an example tab (but all tabs will need to be treated similarly, but separately) so only want to combine those tabs/tables named CFB.
CFB Tab:
CFB Table:
This is an example of how the data will look in CFB tab. each data workbook will contain a new CFB table that may span the same period as the last or may be a different period eg. the next workbook may represent Jan 25-Dec 25 or it could also be a new version of Mar24-Feb25. The tabs can be named consistently and the tables could be named consistently also, if more useful.
The output I want is to have all of the tables joined and un-pivoted with an identifier appended to allow filtering on which Workbook it was.
eg.
I have tried a few AI generated solutions but they seemed to append the subsequent tables as rows rather than joining the months to the same category so the following years/months ended up under the first column headings. I think maybe I need to have the tables unpivoted independently first. Otherwise the AI solutions brought the tables in but couldn't seem to get the data showing, it just had two rows with the workbook name and other metadata.
Appreciate any solutions or nudges in the right direction
Thank you!!
I am hoping to find a solution for the following ( I am very new to Power Query so limited with knowing what is and isn't possible.)
I have multiple workbooks that represent a snapshot of a business overtime.
eg. Data Workbook Jan 24, Data Workbook March 24 etc etc etc.
The workbooks can be created at any point in time and also can represent any point and span of time, so any solution won't be able to rely on fixed dates. Eg. the Jan 24 workbook could have data tables from Nov 23 - Jun 24 - not necessarily starting Jan 24.
The workbooks can all be saved in the same folder. The workbooks will contain common tabs eg. CFB, A&L, Summary (there will be more, but limiting for this example). The tabs will contain tables of data. I want PowerQuery be able to "find" all of the tabs as new workbooks are created and add these to PowerQuery within A SEPARATE file called "Analysis Workbook". The intention is to be able to combine the data and analyse changes overtime. The solution will have to be dynamic enough to be adopted for analysing a different business (different set of workbooks) but each instance of analysis workbook will only be applied to the one folder/set of workbooks. So it won't be able to be pre-determined how many workbooks there will be in the folder, when they will be dated or when new workbooks will be created.
Using CFB as an example tab (but all tabs will need to be treated similarly, but separately) so only want to combine those tabs/tables named CFB.
CFB Tab:
CFB Table:
This is an example of how the data will look in CFB tab. each data workbook will contain a new CFB table that may span the same period as the last or may be a different period eg. the next workbook may represent Jan 25-Dec 25 or it could also be a new version of Mar24-Feb25. The tabs can be named consistently and the tables could be named consistently also, if more useful.
The output I want is to have all of the tables joined and un-pivoted with an identifier appended to allow filtering on which Workbook it was.
eg.
I have tried a few AI generated solutions but they seemed to append the subsequent tables as rows rather than joining the months to the same category so the following years/months ended up under the first column headings. I think maybe I need to have the tables unpivoted independently first. Otherwise the AI solutions brought the tables in but couldn't seem to get the data showing, it just had two rows with the workbook name and other metadata.
Appreciate any solutions or nudges in the right direction
Thank you!!
