Forum Discussion

madrid's avatar
madrid
Helper I
4 years ago
Solved

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 wo...
Tips and Tricks
Tutorial Requests
  • madrid's avatar
    madrid
    4 years ago

    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 

     

edhans's avatar
edhans
Community Champion
4 years ago

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's avatar
madrid
Helper I
4 years ago

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 

 

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    Very cool madrid - I learned something new!

  • Jjoy94's avatar
    Jjoy94
    Regular Visitor
    2 years ago

    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"

     

  • OtavioFB's avatar
    OtavioFB
    New Member
    4 years ago

    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!

  • at1222's avatar
    at1222
    Regular Visitor
    2 years ago

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

  • mpconstantin's avatar
    mpconstantin
    Regular Visitor
    10 months ago

    Hi Madrid,

     

    Your answer was very useful to figure out my request 🙂 I just did a small change in case people need to have the content in Base64:

     

    let
      Source = SharePoint.Contents("https://company.sharepoint.com/sites/SiteName/", [ApiVersion = 15]),
      Lists = Source{[Name = "Lists"]}[Content],
      #"Your Reports1" = Lists{[Name = "ListName"]}[Content],
      Attachments = #"Your Reports1"{[Name = "Attachments"]}[Content],
      #"Expanded Content" = Table.ExpandTableColumn(Attachments, "Content", {"Content"}, {"Content.1"}), this allowed me to have the binary content of the attachments
        #"Changed column type" = Table.TransformColumnTypes(#"Expanded Content", {{"Content.1", type binary}}),
      #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Changed column type", "Attachment64", each Binary.ToText([Content.1], BinaryEncoding.Base64)), {{"Attachment64", type text}}), and here, instead of reading the attachments I encoded it Base64
     
    Hopes this will contribute to anyone looking for this data! 
    Again madrid, THANKS A LOT for sharing this information 🙂
     
    Cheers!