Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
madrid
Helper I
Helper I

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

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

6 REPLIES 6
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

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 

 

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"

 

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

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!

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors