Forum Discussion
Import Excel files from Sharepoint and need the worksheet name as a field
Managed to solve this using the Web connection method on each spreadsheet in the sharepoint folder and going back some steps in the applied steps on each to get the worksheet name as advised in the steps above.
Tried to do it using the sharepoint folder connection method and inserting step to change the type from contents to data but was unable to get it to work.
Thanks for the prompt response. Unfortunately when I use get data from Sharepoint folder and I go back through the Applied Steps, I don't see "Data" of any column that will exposes the worksheet names.
the reason I am using Sharepoint folder as the connection type is once the report is developed and signed off it needs to be moved to another sharepoint folder location with sensitive production data.
- jennratten
Super User
Hello again - when you have your list of files in the SharePoint folder you will see a Content column in which each cell contains a binary object. This is the file content and contains the diverging arrows. If you click that, Power Query will launch it's process to combine the files on your behalf, creating the function, etc.
If you want to do it yourself, you will need to add a new column to convert the binary to Excel.
Table.AddColumn ( YourPreviousStep, "Data", each Excel.Workbook ( [Content] ) )After that, you can expand the newly added Data column to get the file objects, which have the sheet names.
