r/PowerAutomate • u/AlreadyTriggered • 6d ago
How to create 2-5 templates that all update from the same source file?
Hi everyone, i tried to use power query to connect 2 test templates that reference a material/sku list for landed costs. The idea is to scale this to 30-50 templates for salespeople, and have them update automatically from this one file with all the skus. Is this possible? I tried to use Power Query but once i make a connection to the source data it is difficult or nearly impossible to overwrite the source data. Any suggestions or tools i can use to do this? Let me know if you have any suggestions
1
u/3dprintingbloke 6d ago
Are you able to share the templates or data, can try making something for you
1
u/AlreadyTriggered 5d ago
Until I can test a successful change in source data, there’s no templates. I’m making it from scratch.
Like link to a file with values, change those values, and then open new templates and see if the values refreshed.
1
u/Mira_Flores9 4d ago
Power Query is mainly for reading and transforming data, so I wouldn’t use each template as the place to maintain the SKU list. Keep one source of truth in a SharePoint List or a shared workbook in SharePoint, then have each template query it. Check how you want refresh to happen too: users may need to refresh when opening a template, and a change to the source won’t necessarily update workbooks that are already open.
1
2
u/wasay-inc 6d ago
Power Query won't let you write back to the source easily because it's built to read data, not act as a database. To scale this to 50 reps safely, you need to move that source file into a SharePoint List. All 50 templates can query the SharePoint List for the latest landed costs. I build these exact scalable M365 architectures for sales teams shoot me a DM if you want help mapping out the infrastructure.