cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
madrid
Frequent Visitor

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"

 

 

1 ACCEPTED SOLUTION
madrid
Frequent Visitor

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 

 

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
madrid
Frequent Visitor

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 

 

View solution in original post

Very cool @madrid - I learned something new!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors