Import Excel files from Sharepoint and need the worksheet name as a field

[dmccardle]

Hi, I am trying to import some excel files into PowerBI using PowerQuery and need to have the worksheets of each file as a field in the tables created.
I have tried the sharepoint folder method to bring in a group of files and tried the web connection method to bring in each file individually and am not consistently getting the worksheet name in the table being created.
The reason I need to bring in the worksheet name as a field is the files get a new worksheet added everymonth in the format '04-2024' and I am copying the worksheet name into a new column in powerquery to use as a reporting period field, (there is no other date field in each of the sheets I can use).

Can some please advise on what step and where in powerquery I can make sure I am bringing in the filename and the worksheet name?

[dmccardle · Accepted solution]

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.

[dmccardle]

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]

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.

[jennratten]

Hello @dmccardle, 

This step is automatically added when you choose to get data from an Excel worksheet. This is essentially a drill-down into the object with the data.  It does not retain any of the previous rows/columns but replaces it.

If you wanted to keep the worksheet name (or the name of whichever object you got the data from), you can do it by selecting the previous step and expanding the column containing the data, by clicking the icon which looks like diverging arrows in the header of the column containing the object.  This will retain the object kind (table, sheet, defined name, etc) and the object name.

I filtered the table in the snip above to only show the sheet named Sheet1, then expanded and the result is shown below.  I have the data rows from Sheet1 as well as the columns previously displayed with the object kind, name, etc.

I connected to an Excel file locally in this example, however the method is exactly the same regardless of if the files are on your computer or in SharePoint.