cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Import SharePoint online list attachments (Excel sheets) in power BI.

Hi All,

 

I am trying to import excel sheets attached to list items in Sharepoint online.Every list item will have one or more excel sheets attached to it as shown below.

I am trying to figure out a direct way to get these in power query, please help. Thanks in Advance.

 

attachmnts.JPG

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Import SharePoint online list attachments (Excel sheets) in power BI.

Hi @Apurva_M 

When we connect to a sharepoint list using "sharepoint online list" connector in Power BI, It seems we can't get data of the attachment file.

Here is a workaround from a similar thread.

Load data from Excel file attachment in SharePoint online list

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Advocate II
Advocate II

Re: Import SharePoint online list attachments (Excel sheets) in power BI.

I know this is a relatively old thread, but I came up with a solution to this which I thought I'd share in case it helps someone. The key is to split the process into a couple of parts, which I've broken out into four steps.

Step 1 - Import the list data. This includes the column AttachmentFiles, which you expand to get a number of new columns, the useful one being ServerRelativeUrl. Remove any columns you don't need. Grab the content of the first row in this column.

Step 2 - Create a new Web query. For the URL, combine the ServerRelativeUrl with the sharepoint site address to get something like "https://mydomain.sharepoint.com/sites/mysite/mylist/Attachments/1/myfile.xlsx". This should grab the Excel data, which you can then manipulate as necessary to achieve the formatted data you need.

 

Step 3 - Convert the query to a function. You do this by opening the query in advanced mode and adding the parameter line to the beginning. Also, modify the url to accept the parameter, like this:

 

(fileName as text) => 
let
    Source = Excel.Workbook(Web.Contents("https://mydomain.sharepoint.com/" & fileName), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    ...
    Data manipulation steps
    ...
in
    #"Name of final step"

 

Save the function with a sensible name, e.g. fnGetExcelData

 

Step 4 - Go back to the list query and add an Invoke Custom Function column. Pass the ServerRelativeUrl column in as the fileName parameter to the function you created. Expand the resulting column.

 

And that's it! You now have the Excel data from the attachment, along with any other list columns from the list. Although this will work only if all list attachments are the same Excel format, you could in theory have different types of file simply by making multiple calls to the list, and using a different function for each file type. As long as you had a list column that specified what type of Excel file it was, you could import lots of different types of Excel data from the one list.

 

I hope this helps someone.

 

Duncan

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors