SharePoint list with attachments fails to refresh in PBI Desktop

[Anonymous]

Hi all,

TL;DR : SharePoint online list with attachment fails to refresh in PBI Desktop : how can I get the attachments URL ?

I've been having some trouble lately refreshing a PowerBI Desktop report from a SharePoint Online list, the refresh is stuck and never ends (even when I let it run for an hour using a stable connection). It used to work quite well but my SP list considerably increased since the first draft of my report (around 2k items), the online report kept refreshing OK through the service though.
I found out that this is caused by the attachments in some of my SP list items : In my request through the SP online list connector, I expanded the column AttachmentFiles to get the url of the first attachment (if any) using the column ServerRelativeUrl (which is the information I want to end up with : just an URL to the attachment, not the actual binary content of the file). When I remove this one column, the refresh is swift and works well. 

Does anyone have any advice on how I could pull the attachments URL from my SP list ? Using the conventional SP connector does not seem to do the trick. 

I've tried using the REST API described here and I can manually retrieve an attachment URL from a single specified item in the list, but I don't know how to make it work for ALL the lines with attachments in the list directly from PowerBI. 

Thanks a lot ! any advice is welcome

[Daryl-Lynch-Bzy]

Hi @Anonymous 
If you are having trouble loading SharePoint lists can I suggest that you check the Implementation version for the SharePoint connector - https://docs.microsoft.com/en-us/powerquery-m/sharepoint-contents.  I find that Implementation="2.0" is more stable that Implementation="1". 

Read this for more info Sharepoint List 2.0 beta connector 

To get the URL for the Attachment or Attachments you need to use the HTML.Tables function to extract the href.  The Microsoft guide has an example:  https://docs.microsoft.com/en-us/powerquery-m/html-table.  Example 2.  Chris Webb has blogged about this.  https://blog.crossjoin.co.uk/2018/08/30/power-bi-extract-urls-web-page/

Once you have the URL you can find the file and open in another step.

Many thanks

Daryl

[Anonymous]

Hello Daryl and thanks for your reply!

I have indeed been using the first implementation of the SPO connector. I tried switching to the v2.0, either by creating a new request from scratch or by altering my current one : using the v2 doesn't fetch all the additional columns that the v1 collected (AttachmentFiles, ContentType, FieldValuesAsHtml, Properties and so on). The only metadata I have is about people creating or editing the SP list items. I tried with both display modes in the SPO request wizard : default view-only or all, the result is the same. 

What should I pass as argument to call the function HTML.Tables ? I don't see any information about the attachments is being pulled by the request, besides the attachment count, and I can't expand any column

Thank you very much

[Daryl-Lynch-Bzy]

I agree there is less metadata available for people.  It is limited to title, email, sip, picture.  not location and job title.  This would need to be sourced separately.

[Daryl-Lynch-Bzy]

@Anonymous 

Sorry, but the new version has simplified the API so a lot of those previously nested columns like "FieldValuesAsHtml" have been dropped.  Most like the cause of the performance issues. 

Generally there is an new alternative, so I would expect AttachmentFiles to be visible.  In a recent example I was working on, the attachment file contained an HTML string like the following:

<Div> f
<a><a href>url path 1</a href>attachment name 1</a>
<a><a href>url path 2</a href>attachment name 2</a><a><a href>url path 2</a href>attachment name 3</a>
</div>

This is what the new connector provided, so Html.Table was required to read the individual a rows.  Otherwise you can use the old-fashion delimiters.  Other Sharepoint features like People, Choices are available in a different form.

Table.AddColumn(#"Filtered Rows", "Html Text", each Html.Table([AttachmentEmail] , {{"Attachment Name", "a" }, {"Attachment URL", "a", each [Attributes][href]}}, [RowSelector="a"] ))

The table was then expanded to following:

[Anonymous]

@Daryl-Lynch-Bzy thanks for taking the time to help

Sorry but I don't understand which row you are using to derive all of this content about the attachments from. I don't see any related column, nor any html text or column to be expanded and lead to this info.. 

to be clear this is what I did : 
1. Use the SPO connector (implementation 2.0) to fetch my SPO list items. This resulted in two applied steps : 
Step "source" 

= SharePoint.Tables("https://XXXXX.sharepoint.com/sites/XXXXX", [Implementation="2.0", ViewMode="All"])

Step "navigation"

= Source{[Id="XXXXXXXXXXXXXXX"]}[Items]

 2. Now the request provides a table with the columns in my list + a few other metadata-related column (like 'Created by', 'modified by', 'version', 'number of attachments', and a few other) BUT there is nothing related to the attachments URL, the item contents as html, a table to expand in a column or anything nested.. 

What am I missing ?

[Daryl-Lynch-Bzy]

I checked my solution.  It appears the person sho created a list added a extra column called AttachmentEmail, so this contained the HTML string with the URL to where the document was saved on OneDrive. 

The table did include an system column for Attachments.  But this was populated with 0.   Looking on the internet.  There is suggestion that this will just be the number of attachments, but not the details of the attachments.  It looks like an Attachment Files column is required, not just the attactments.   

Sorry I can't help.

[Anonymous]

I had the same system column and it is indeed showing the number of attachments to the list item

That's okay thanks for helping anyway ; much appreciated !

[Anonymous · Accepted solution]

so I found a workaround : 

I created a power automate flow that loops through the list items with attachments, reads the last attachments URL, then writes this url value in a dedicated text column within the list (that I had to add). The flow takes less than 5 mins to run and the powerbi refresh works well (it doesn't fetch the AttachmentFiles column anymore).

if anyone knows how to get rid of this hack and do it properly from powerbi please let me know

regards