Showing results for 
Search instead for 
Did you mean: 
Helper IV
Helper IV

Updating Static Spreadsheet based off selection/liveFactTable? Power Automate? How to approach?

Hey all,


Not sure if this is a Power Automate/Power App related solution, but I figured I'd try. I have a colleage that created a live report on Power BI Service, which pulls data from one of our system which provides information on Employee hours worked, which client's they've been working on, utilization, which projects, forecasting, capacity, etc. 


So all in all, it's just tracking what clients/projects the consultants are working on. Within the company, there are a subset of clients that have opted in for a aaS offering, meaning that out of the 60 clients we have in the company, 20 of those are managed by my team.


To start, I manually created a dimension spreadsheet which lists all of our clients (20 aaS), and a couple of fields that would be useful for us (which are static) - I created a basic report off of this, but knew that we needed to leverage the live data my colleage is pulling.


Since we recently had this update, I was able to DirectQuery to the dataset from Power BI service to Power BI Desktop. I made sure that the client names were identical, and connected my dimension table (20 distinct clients/manual/static) to the live dataset which has a column 'Client' (60+ clients company wide). Yes, unfortunatly, we're connecting string on string.


Everytime our aaS group acquires a new client, I would have to be in the know, and then go and manually add it to our dimClient spreadsheet and fill in whatever fields are there. Is there any decent way of keeping track of when new client poppulates in the factTable (live data)? Better yet, is there an easy way to be able to autopopulate a client from the fact table to my dimension table via a manual trigger? I'm thinking maybe something in Power Automate with a manual trigger?


A drop down menu of all the distinct client names I can choose from, select, then enter fields in Power Automate? I'm just spitting ideas out there, but if anyone has any ideas or insights on a weird situation like this, I'd appreciate it.


If you made this far into my rambling, thank you!


Helpful resources

Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors