Power Query to combine file path with file name and add cell reference to extract inf

[Shyland]

Good morning all.

I am currently trying to get information for multiple (100's) of files within SharePoint. I have used Power Query to show all of the files, the folder path, extension. What I really want is to append the tables contained in within all of the workbooks.

I haven't found away to show all of the tables in the files but I was wondering if I could use Power Query to concatenate the file path, file name, name of the worksheet, and cell reference to automatically extract the information and then Close and Load information to a single table.

Thoughts and suggestions would be greatly appreciated!

Thank you

 

[Ken Puls]

When you say "files", what kind of files are you talking about? CSV files, text files, Excel files or something else. Are there a mixture of file types in the same folder, or are the file types consistent?

What you want should be possible, we just need a bit more information in order to help you out here.

 

[Shyland]

When you say "files", what kind of files are you talking about? CSV files, text files, Excel files or something else. Are there a mixture of file types in the same folder, or are the file types consistent?

What you want should be possible, we just need a bit more information in order to help you out here.

Thanks Ken.

Currently, the files are a combination of .xlsx, xlmx, xltx files.

I can get Power Query to drill down through the file folders on sharepoint and show all of the files, the path and the file types but I can't get to the table located in each file type.

Thanks!

 

[Shyland]

Here is a screen shot of the files from Power Query with the file path.
I have tried a couple of this from your book, M is for Data Monkey but haven't got this one nut cracked yet. If needed, I can change the files types to all be .xlsx but I will not be able to save them as .csv since there are multiple worksheets in the workbooks Ken.

Thanks again!

 

[Ken Puls]

So adding a custom column using the following function doesn't work?

Add Column --> Add Custom Column
FOrmula: =Excel.Workbook([Content])

 

[Shyland]

Page 48 in "M is for (Data) Monkey". It is working Ken. I am having to play with the resulting data to get to the information but I believe that this is going to get me to the data that I need to get to Ken.

A huge thank you!

Your and Miguel's book has made me a rock star at work.