Get and Transform Excel files from SharePoint list

[madrid]

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"

[edhans]

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.

[madrid · Accepted solution]

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 

[Jjoy94]

Worked for Me! Thanks for your help. The only correction is the parenthesis for the Attachments. After this step, we can expand the Content and combine the Binary. Power BI automatically creates the Sample file for transformation and we can make changes to the sample file.

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]

in
 #"Attachments"

[at1222]

Hi, I've attempted the solution you have provided and I am stuck. Can you go more in depth on this solution? Thank you.

[OtavioFB]

Hi Madrid!

Can you kindly give me more details, maybe with an end to end example. I am trying to do what you described, but no success for now...

Best Regars!

[edhans]

Very cool @madrid - I learned something new!