Budgeting Data Collection
This example show-cases an end to end process automation for decentralized data collection for budgeting purposes. Each department will get its own file and the link to the file will be sent by email. The files are generated by Power Automate.
1) In the first step, a proper template needs to be designed. Keep in mind that it needs to include supporting columns that will simplify data import in the last step.
2) Next, we set up a steering table. In this case each row represents a department with its responsible people. Additionally, there is a placeholder column that will later automatically be populated with the link to the respective file.
3) Afterwards, we build a Power Automate workflow that connects to the steering table, extracts the data and generates the template file, populate it with the data, generates the link to the file and sends the link to the email address/addresses provided in the steering table.
4) Finally, we can connect either Excel or Power BI to the respective folder and import the files using Power Query. As previously mentioned, the template needs to be designed in a way that Power Query can robustly transform the data. This is l best achieved with supporting columns thar are hidden to the user.
If you are interested in this workflow or would like to adjust it to your use case, feel free to contact us.