Budgeting process workflow

[Felpan]

Hi I am looking for some ideas/videos/blogs to automate my workflow. My company use microsoft 365, but powerapps is not a option.

I will explain how the process is now:
1. Export a masterfile
2. Split masterfile (using vba code, not my code). Is this possible to do with Power query?

3. Exported and splittet excel files moves to different folders in a Sharepoint library. One excel file represent one subdeparterment. In total I would estimate 600-700 excel files. The files are placed in a shared folder so the manager can input data. So a head department would have like 6-8 folders, one for every subdepartment, with 6-8 files where manager have to input data.

4. Collect all the excel files to create one masterfiles for every headdepartment, one query for every subdepartment. To do this I have to manual update multipe to link to the right folder. 

[Keezz · Accepted solution]

PowerQuery may be able to help but not solve everything.

• The splitting of a master file is better left to VBA
  Powerquery can not create new worksheets or workbooks automatically.
  And it certainly can not create folders
• Collecting the data is where PowerQuery can help
  • It can get all the folders and filenames with SharePoint.Files()
  • It can consolidate all excell files into one big dataset
  • Do analysis, transform the data
• Distributing the results is better left to VBA or PowerScript, again, powerquery can not automatically create new files or folders.
• I am not familiar with the PowerBI capabilities to create dashbards and only show the data for a particular department, but I am pretty sure it can be done.
  But you would probably need a corporate license for Power BI though.

[Anonymous]

Hi @Felpan 

Based on your descriotion, you need to split some file, can  you provide some sample  data and the output that wht is the format you want to get after spliting?  based on the sample data can provide moe suggestion for you.

Best Regards!

Yolo Zhu

[Felpan]

Thanks, but I can not share company data. But if you create a ID column and some dummy value, how do you split this in to two query?

ID	Value
1	100
2	200

[Anonymous]

Hi @Felpan 

Thanks for your quick reply, and in power query, it cannot split the value to multiple querises based on one table.

You can refer to the following similar thread.

Export n files in folder as n different query - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.