Forum Discussion
Get and Transform Excel files from SharePoint list
Hi there,
I have a SharePoint list for user to update information.
Each list item include some data (in list columns) and an attachment excel file (with fixed format, data in first tab of the workbook).
My objective is to loop through the list, get all attachments and combine all data into a big Table.
I was able to read the list and extracted all relative/absolute url of all attachments (see code below).
But I don't know what should be the next step to combine data in all attachments into a big Table.
Please advise me a solution!
Thank you,
Md
let
Source = SharePoint.Tables("https://company.sharepoint.com/sites/SiteName/", [Implementation=null, ApiVersion=15]),
#"SomeName" = Source{[Id="id-of-the-list"]}[Items],
#"Expanded AttachmentFiles" = Table.ExpandTableColumn(#"SomeName", "AttachmentFiles", {"FileName", "FileNameAsPath", "ServerRelativePath", "ServerRelativeUrl"}, {"AttachmentFiles.FileName", "AttachmentFiles.FileNameAsPath", "AttachmentFiles.ServerRelativePath", "AttachmentFiles.ServerRelativeUrl"}),
#"Added Custom" = Table.AddColumn(#"Expanded AttachmentFiles", "Custom", each "https://company.sharepoint.com" & [AttachmentFiles.ServerRelativeUrl]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "FullURL"}})
in
#"Renamed Columns"
Thanks Edhans for your advice.
Luckily I was able to figure out how to achive my goal within Power BI.
Here is how to read the SharePoint list, extract attachments and combine them into one big table:
Top:
let Source = SharePoint.Contents("https://company.sharepoint.com/sites/SiteName/", [ApiVersion = 15]), Lists = Source{[Name = "Lists"]}[Content], #"Your Reports1" = Lists{[Name = "Your SP List"]}[Content], Attachments = #"Your Reports1"{[Name = "Attachments"]}[Content],After this step, we have a column called "Content" that contains Table information of attachments.
Then use: Table.ExpandTableColumn to expand that table, give us the Content.Content with type Binary.
Then use: Table.ExpandTableColumn again on "Content.Content", give us "Data" column of each attachment
Then use: Table.ExpandTableColumn again on "Data" column to get combined table of data.
Along the way, Power BI generates "Transform File" "Sample File" functions automaticly.
Cheers!
Md
7 Replies
You cannot do this with Power BI. Power BI via Power Query can combine files in a SharePoint library, but not attachments in a list. You may be able to use Power Automate to cycle through the list and extract the attachments and save them to a folder (library) for consumption. If that is possible, you'd need to control the Power BI refresh from Power Automate so it only ran after the folder was populated. You should check out Microsoft Power Automate Community - Power Platform Community if you want to head down that path.
Thanks Edhans for your advice.
Luckily I was able to figure out how to achive my goal within Power BI.
Here is how to read the SharePoint list, extract attachments and combine them into one big table:
Top:
let Source = SharePoint.Contents("https://company.sharepoint.com/sites/SiteName/", [ApiVersion = 15]), Lists = Source{[Name = "Lists"]}[Content], #"Your Reports1" = Lists{[Name = "Your SP List"]}[Content], Attachments = #"Your Reports1"{[Name = "Attachments"]}[Content],After this step, we have a column called "Content" that contains Table information of attachments.
Then use: Table.ExpandTableColumn to expand that table, give us the Content.Content with type Binary.
Then use: Table.ExpandTableColumn again on "Content.Content", give us "Data" column of each attachment
Then use: Table.ExpandTableColumn again on "Data" column to get combined table of data.
Along the way, Power BI generates "Transform File" "Sample File" functions automaticly.
Cheers!
Md
