Forum Discussion
Retrieving data from file uploaded to SharePoint list
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 is4) 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
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
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,
PaddyHi 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 is4) 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
Brilliant, thanks very much Pete. Works perfectly and easy to follow your instructions.
Paddy
