In Power Query, when combining/appending multiple tables is there a way to add a column which will give the name of file/table the data is from.

[Prodigal_nerd]

I am trying to combine tables from 10 SharePoint excel files. Each file is named as per the owner who works on the file. I want to add a column after combining/appending all 10 tables into one, which will give me the file's/tables name (ie owners name) for example the first 100 rows of data are from John's file and next 100 rows from Mary's file and so on... So the column should have "John" for the first hundred rows and "Jane" for the next 100 rows

 

[AliGW]

If the table names in the individual files contain the owner's name, then you will get that in a column on the right when you append the tables.

Provide a sample file.

 

[Prodigal_nerd]

I just checked the table names, it looks like only the file name has the owners name and all table names are the same
But I can rename the table names to include the owner name in it

 

[p45cal]

Take a look at

There are a few additional tricks needed to filter the files/tables you need and to retain other attributes in your final consolidated table, for example, the file name.
How far have you got?

 

[p45cal]

Cross post: https://www.mrexcel.com/board/threa...-name-of-file-table-the-data-is-from.1256653/

 

[GraH - Guido]

I just checked the table names, it looks like only the file name has the owners name and all table names are the same
But I can rename the table names to include the owner name in it

Get data -> From File -> From SharePoint Folder
Paste in the site path (the root)
In the Source step in the formula bar Change SharePoint.Files to SharePoint.Contents.
Drill down on the correct library, and filter later the correct folder.
You will see something alike as the screenshot, where "Name" is the file name.


On the Content column (containing binary data type) you can invoke a function to read from Excel.
Something of this sort: = Table.TransformColumns(#"Filtered Rows1",{{"Content", Excel.Workbook, type table}})

Now expand the columns.

It can work.