Co-Authoring and combining worksheets in a workbook

[Craigc3814]

I created an excel file that has multiple worksheets in it (One for each employee that uses it). The employees go in update their data and it all appends together and allows me to make a dashboard.

It worked fine until I added my file to OneDrive in hopes of allowing people to coauthor.

I used a formula Ken Puls created years ago and I think that is my issue.

Source = Excel.Workbook(File.Contents(FullFilePath)),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] =
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5",

It will not run now because the file is open, I did some research and saw that if you filter out files that start with ~ that it will solve the issue but I cannot do that.

Is there a better way to append all sheets in the workbook you are using that I have yet to learn?

 

[Matt Allington]

If co-authoring. I recommend having 1 file Per person.

https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

i think it is still good to filter out files starting with ~. Just put a filter on the file name column and remove “starts with ~”