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

Shyland

New member
Joined
Dec 15, 2016
Messages
4
Reaction score
0
Points
0
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
 
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.
 
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!
 
Example.PNG

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!
 
So adding a custom column using the following function doesn't work?

Add Column --> Add Custom Column
FOrmula: =Excel.Workbook([Content])
 
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.
 
Back
Top