Puzzled on Best Way to Model Excel data

[Anonymous]

Hi Folks,

Thanks in advance for help.

I have the following extract from Workday that I'm loading into Power BI from an Excel file to help do merit planning.

EE ID	Name	Manager	Salary	Proposed Increase	Effective Date
123456	Scooby Doo	Fred Jones	100000		1/1/2021
654321	Velma Dinkley	Fred Jones	110000		1/1/2021
196124	Shaggy Rogers	Daphne Blake	85000		1/1/2021

Each Manager will get their own Excel extract to enter the proposed salary increase the worker is getting. All fields will be locked from edit except the Prosposed Increase column. For example, Fred Jones would receive the following Excel template:

EE ID	Name	Manager	Salary	Proposed Increase	Effective Date
123456	Scooby Doo	Fred Jones	100000		1/1/2021
654321	Velma Dinkley	Fred Jones	110000		1/1/2021

Daphne would receive this Excel template:

EE ID	Name	Manager	Salary	Proposed Increase	Effective Date
196124	Shaggy Rogers	Daphne Blake	85000		1/1/2021

I would be keeping each individual managers template in a Shared folder and then only sharing the individual files with that manager through OneDrive. So for example, Fred would get a file named Fred Jones with his two workers on it. He would populate the proposed increase and then close the file once he's done. 

EE ID	Name	Manager	Salary	Proposed Increase	Effective Date
123456	Scooby Doo	Fred Jones	100000	4%	1/1/2021
654321	Velma Dinkley	Fred Jones	110000	5%	1/1/2021

I then want Power BI to pick it up and populate it in POwer BI on refresh like this (This assumes Daphne hasn't done hers yet):

EE ID	Name	Manager	Salary	Proposed Increase	Effective Date
123456	Scooby Doo	Fred Jones	100000	4%	1/1/2021
654321	Velma Dinkley	Fred Jones	110000	5%	1/1/2021
196124	Shaggy Rogers	Daphne Blake	85000		1/1/2021

I have to do this for 60 managers. The only way I can figure out how to do this is to APPEND 60 different Excel tables on top of each other in order to make this work. In my real data set, there are 5 fields they will need to fill out, not 1. 

Is that the only way I can do it or is there another way to make this easier? 

Many thanks for the help!!! 

[OKgo · Accepted solution]

Data > Get Data > Folders
Point at the highest level folder that contains all 60 manager sub-folders

Complete the sample file wizard to train PQ what the format/headers of the spreadsheet are

It should look pretty good on import.

I do this all the time for CSV and Excel files and it works great

[Anonymous]

Oh...My...God. Is it really that simple? I just tried that. It worked wonders. 

I was LITERALLY going to sit there and append 50+ docs on top of each other.

You are a genius. Thanks. 🙂

[OKgo]

Glad it worked and there was not some barrier to stop it