Forum Discussion
PowerBI For Capacity Modelling Dashboard
Hi All,
I'm just getting into the world of Power BI. Absolute beginner.
I'm involved at work with producing numerical Capacity Models that inform factory design requirements based on a number of inputs and data sources. I use excel for this with VBA to automate some of the processes.
It's a frequent occurrence that I will sit in a meeting with the client and manipulate variables and parameters in real time, run the model and produce new outputs in the form of graphical data representations. This can happen several times in one meeting.
I have found PowerBI to be significantly better in producing professional looking and interactive data visuals, so I'm really keen to use it as a dashboarding tool to interface with my Excel based capacity models.
Better still, I would like to be able to provide the client with access to said dashboard so they can interact with the model itself. However, this may be something to develop later on if I can persuade my business to invest in Pro or Premium. We don't have access to Power BI Pro or Premium at the moment, so everything works 'Locally' for the time being.
I can foresee 2 main challenges:
1. If I want PowerBI to update in real-time based on my Excel-based capacity model. I have to refresh the data every time the model is updated. Is it possible to automate this with VBA? PowerBI requires that the Excel workbook is closed before it loads the data.
2. I would like for parameters set on a drop-down in PowerBI to be read and loaded into the excel model. I think this is possible to Connect to Data in Power BI from Excel.
Interested if you think this is possible or if I'm trying to make a "square-peg fit a round hole" by using Power BI for this task.
Thank you!
Power BI can be an excellent tool for visualizing and analyzing data, and can be integrated with Excel for a more complete solution. Next, I'll address your two main challenges:
Automate data refresh from Excel to Power BI: In Power BI, you can use the Get Data feature to import data from Excel. However, as you mentioned, Power BI requires the Excel workbook to be closed before loading the data. To overcome this limitation, you can use the Power Query add-in in Excel to transform and load data from Excel into Power BI. Power Query lets you create queries that connect to your Excel data, apply transformations, and load the results into Power BI. You can use VBA to automate the process of updating queries in Power Query. This will allow you to refresh the data in Power BI while keeping the Excel workbook open.
Read and load parameters from Power BI to Excel: Power BI lets you create parameters and use them in your visualizations. If you want these parameters to be read and loaded into your Excel model, you can use Power Query again. Create a query in Power BI that extracts parameter values and, through Power Query, loads those values into a table in Excel. You can then use those values in your Excel-based capacity model. This technique allows you to maintain a connection between Power BI and Excel to transfer data, including parameters.
In short, it is possible to integrate Power BI with Excel and address your challenges using Power Query and VBA. With this combination, you can automate refreshing data from Excel to Power BI and transfer parameters from Power BI to your Excel-based capacity model. As you explore these options, it's a good idea to test and validate your solution to make sure it fits your specific needs.
1 Reply
Power BI can be an excellent tool for visualizing and analyzing data, and can be integrated with Excel for a more complete solution. Next, I'll address your two main challenges:
Automate data refresh from Excel to Power BI: In Power BI, you can use the Get Data feature to import data from Excel. However, as you mentioned, Power BI requires the Excel workbook to be closed before loading the data. To overcome this limitation, you can use the Power Query add-in in Excel to transform and load data from Excel into Power BI. Power Query lets you create queries that connect to your Excel data, apply transformations, and load the results into Power BI. You can use VBA to automate the process of updating queries in Power Query. This will allow you to refresh the data in Power BI while keeping the Excel workbook open.
Read and load parameters from Power BI to Excel: Power BI lets you create parameters and use them in your visualizations. If you want these parameters to be read and loaded into your Excel model, you can use Power Query again. Create a query in Power BI that extracts parameter values and, through Power Query, loads those values into a table in Excel. You can then use those values in your Excel-based capacity model. This technique allows you to maintain a connection between Power BI and Excel to transfer data, including parameters.
In short, it is possible to integrate Power BI with Excel and address your challenges using Power Query and VBA. With this combination, you can automate refreshing data from Excel to Power BI and transfer parameters from Power BI to your Excel-based capacity model. As you explore these options, it's a good idea to test and validate your solution to make sure it fits your specific needs.