Retrieving data from file uploaded to SharePoint list

[Syndicate_Admin]

Hi,

I have created a SharePoint list where users will upload an Excel file every time they make an entry to the list. I am using a list rather than just a file folder just for greater control of the meta data.

I would like to retrieve the data from the uploaded Excel files in the list in the same way that I would retrieve the data from a file folder. Is this possible with Power Query? When I query the SharePoint list there isn't a 'content' column in the same way that you would have with a SharePoint file folder.

Any help would be much appreciated

Thanks,

Paddy

[Syndicate_Admin]

Hi @paddyg95 ,

Going to need a bit more info here:

- Are you adding the actual files (attaching/embedding) to the SP list, or just URLs that point to the files?

- If you're adding/attaching/embedding the actual files, what column/data type are you using in the SP list to enable this?

- If you're only concerned with additional metadata, might it be feasible to use Power Automate to detect file changes in a folder and write your metadata to a SP list instead?

Pete

[Syndicate_Admin]

Hi Pete,

thank you for the reply, much appreciated.

I am attaching the actual files and would like to get the data from those files, so below is a screenshot of how that looks in the list. For context, the SP list is for users to upload forecast files and I would like to get both the metadata from the list (e.g. the forecast date entered by the user on the list row), as well as the file contents from the uploaded file, but it's the file contents I'm struggling with.

Many thanks,

Paddy

[Syndicate_Admin]

So, you can get the same exact metadata columns on libraries as you can on lists. They are added the same way, either from the view or from library settings. If you are trying to store files to extract data from, I would definitely use a library instead. The library will let you pull the file metadata with the OData connector (I did a tutorial on how to do this here: https://youtu.be/yeIQ9jKln_I).

That way you'd be able to use the SP Folder connector to pull the files. 

[Syndicate_Admin]

Hi Paddy,

Try this:

1) Connect to your SP list using the SharePoint Online List connector in Power Query and choose the 1.0 Implementation. This is important as the 2.0 version won't give you the [AttachmentFiles] column that you need.

2) Expand the [AttachmentFiles] column files selecting the [ServerRelativeUrl] column to expand.

2.5) Important - at this point, filter the list to ensure only attachments that have the same data structure remain.

3) Create a new custom column like this:

"https://yourcompany.sharepoint.com" & [ServerRelativeUrl]
//Obvs change "yourcompany" to whatever your actual URL is

4) Copy one of the complete URLs from your new column, open the Web connector, and connect to this URL. Complete any transformations you want to do on all of the attachments in this query.

5) Open Advanced Editor for this Web query and copy ALL of the M code from there. Go back to your SharePoint query and add another custom column.

6) Paste the M code into the formula window for this custom column, but change the URL to reference your full URL column instead, like this:

Now, when you click OK, you should get a column with nested tables that contain each of your transformed attachments that you can expand to append them all together:

Pete

[Syndicate_Admin]

Brilliant, thanks very much Pete. Works perfectly and easy to follow your instructions. 
Paddy