Auto update two excel files

[Paramedic_Dave]

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

[christinepayton]

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-- 

[lbendlin]

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.