Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Ive spent many hours searching the internet and trying various things but no success. Any help would be amazing.
So the problem is fairly simple, it appears the soultion is more difficult
I have two excel spreasheets that sit on Sharepoint. (which is where i think my first difficulty comes from)
One of them has personal data about my staff (name, email, telephone number etc)
The second has their training records on
What I want to be able to do is link the two sheets, so that when I add a new member of staff to the main sheet, a new row is automatically created and their name pulled in on the second sheet.
The problem i cant get round is when I have tried Power query, formulas, links etc I can make the second sheet update the name list but the rest of the data in the second sheet doesnt move.
As an example lets say sheet 2 looks like this:
Name | Induction |
Andy | Yes |
Steve | Yes |
Jane | Yes |
I add a new member of staff to my master sheet, save and close. Open up sheet 2 and refresh and i get this
Name | Induction |
Andy | Yes |
Bob (new) | Yes |
Steve | Yes |
Jane |
In a perfect world I would like it to look like this and as an added bonus it copying the formulas from the above cells into the newly created row as well.
Name | Induction |
Andy | Yes |
Bob (new) | |
Steve | Yes |
Jane | Yes |
Is it possible????
Thanks
You want Power Automate for this, not Power BI. 🙂
It will be much easier for you if you use SharePoint lists to hold the data rows instead of Excel. You can create lists based on the Excel sheet to make this easier. The benefit of the SP list is that there is a Power Automate trigger for "when a new item is added to a SharePoint list", so that you can have actions performed when that happens. You need to have something reliable like this to trigger on or you'll make it really painful for yourself.
Once you have that automation set up, you can connect Power BI to the lists if you want to report on the resulting data--
Possible, but with other tools. Power Query is called Power Query because its purpose is to query data from a data source, not to write anything to that data source (although people sure try to find all kinds of creative tricks around that).
Use Power Automate and / or office scripts.