Append Queries - Add Source Table Column

[alexamatthews]

I would like to have a column in an appended table which details the original table. For example, I have timesheets for individual team members which are appended to one table with all recorded time. I would like to be able to view that data by project, sub-project, and also team member, so I could see that 20 hours was spent on the build phase of project X, of which 10 hours was Dave and the other 10 Bill. At the moment I can see the project & sub-project, but not the name of the team member which is only given as the title of the original table. Unfortunately I can't change the source data to have an additional column of team member name.

Dave Timesheet

Project X	Build	10
Project Y	Test	5

Bill Timesheet

Project X	Build	10
Project Y	Test	5

Combined Timesheet

Dave Timesheet	Project X	Build	10
Dave Timesheet	Project Y	Test	5
Bill Timesheet	Project X	Build	10
Bill Timesheet	Project Y	Test	5

Is this possible? I can't seem to find anything on it. 

Many thanks

[v-yuta-msft · Accepted solution]

@alexamatthews ,

Create a Folder and put the excel files into the folder, then connect to the folder-> Load. In query editor, you will get below:

 

Then click the expand icon in the Content column, you can achieve the final result.

 

Community Support Team _ Jimmy Tao

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

[amitchandak]

Refer, these power query solution. They will work in power bi too

https://chandoo.org/forum/threads/powerquery-append-include-column-with-sheet-name.31480/

https://www.mrexcel.com/board/threads/add-custom-column-with-sheet-name-as-value-content-power-query...

Then Append :https://radacad.com/append-vs-merge-in-power-bi-and-power-query