I'm going to try to describe my situation and the solution I've come up with. I'm not sure it's the optimal solution so if there any best practices that I'm overlooking, please let me know.
Situation: My boss wants a consolidated P&L workbook(lets call it a "hub") showing multiple regions and business units. She also wants comments visible from specific regions or business units if selected. We have a team of employees who handle various regions independently and will require their own workbooks ("spokes").
My proposed solution: I've created the Consolidated P&L workbook using PowerQuery to pull from my source and manipulate it to the format required. I'm using Power Query to pull data in from a text file. I can use Power Query to reach out to each of the individual region workbooks and consolidate into one dataset for PowerPivot.
One of the challenges I'm facing is when setting up one of the "spoke" workbooks. For performance reasons and business reasons, I want to limit the dataset imported into that book to only the specific region. (I'm still using the original text file) I could set the parameter in PQ, but when setting up a new region, this would require changing the query which creates a nightmare in the data model. There are numerous relationships and formulas which get "blown up" if the dataset has to be disconnected and reconnected.
A solution I found was to use Power Query to create a field called Region in my dataset table and then use the table filter built into Power Pivot to only allow whatever region I choose to be imported.
Is there a better way to set up the "Spokes" so that if we were to acquire a new division/region, there would be a simple set up? I'm using Excel 2013 - 32 bit.
Any advice is appreciated.
Situation: My boss wants a consolidated P&L workbook(lets call it a "hub") showing multiple regions and business units. She also wants comments visible from specific regions or business units if selected. We have a team of employees who handle various regions independently and will require their own workbooks ("spokes").
My proposed solution: I've created the Consolidated P&L workbook using PowerQuery to pull from my source and manipulate it to the format required. I'm using Power Query to pull data in from a text file. I can use Power Query to reach out to each of the individual region workbooks and consolidate into one dataset for PowerPivot.
One of the challenges I'm facing is when setting up one of the "spoke" workbooks. For performance reasons and business reasons, I want to limit the dataset imported into that book to only the specific region. (I'm still using the original text file) I could set the parameter in PQ, but when setting up a new region, this would require changing the query which creates a nightmare in the data model. There are numerous relationships and formulas which get "blown up" if the dataset has to be disconnected and reconnected.
A solution I found was to use Power Query to create a field called Region in my dataset table and then use the table filter built into Power Pivot to only allow whatever region I choose to be imported.
Is there a better way to set up the "Spokes" so that if we were to acquire a new division/region, there would be a simple set up? I'm using Excel 2013 - 32 bit.
Any advice is appreciated.
