Changing input sheet from with power bi and email user

[Ishall1968]

So here's my problem - looking for ideas on how I might approach

picking up a legacy process and bringing it into Power BI: -

32 separate source data sheets from different sources (all excel)

each sheet has the main data sheet and then tables behind it for limiting the input values

held on a share point site which users access and update information that then goes into Power BI to generate reporting etc.

 I need a way to be able to add criteria to the drop downs on all tables on an automated basis - e.g not manually editing the 32 input sheets and also a way of running validation on users against a master list then emailing a relevant email box to ask the user to correct / update the sheet if a user is added or removed,,,,

I can't change the use of the existing sheet (I.e process has to remain as an excel sheet on a share point site)

each sheet carries personal data so I also cant have a single template

any thoughts about whether this is even possible?

[v-xicai]

Hi @Ishall1968   ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @Ishall1968 ,

Recommend all of the excels be saved in the same SharePoint Online Folder, then you may use SharePoint Folder connector in Power BI Desktop to combine these excels, see more details: Combining Excel Files hosted on a SharePoint folder with Power BI.

For the demand that sending a relevant email box to ask the user to correct / update the sheet if a user is added or removed, the users can enter into the SharePoint Online site of saved excel, add new List, renamed it, for example the list "CXJ". Then you can add new items in the new list, and set Alert for the users.

Best Regards,

Amy 

Community Support Team _ Amy

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