How to retrieve binary files specified in a Sharepoint list?

[Anonymous]

I want to put images in my reports, including paginated reports, published via the Power BI Service. I accomplished this by saving some images to a Sharepoint folder, accessing them via the Sharepoint.Folder Data Connector, transforming each into text and following Chris Webb's article on Storing Large Images In Power BI Datasets.

It works, but I also want easy control over associated data/metadata that I can use to control, group & filter the data source. For example, we use a Managed Metadata column to attach an ISO 3166 Country-Subdivision code (e.g. 'CA-ON') to a set of flag images and use it to relate the flag record to other data. I can create additional columns in my Sharepoint folder, but while Sharepoint.Folder retrieves the binary (as [Content]) plus the filename, extension, folder path, etc., it does not retrieve any other columns.

I then tried creating a Sharepoint list. It is even 'cleaner' because I can upload my files to an 'Image' column, which abstracts away the filename (my original solution had me paste 'Canada.png' into the folder, then remove the extension). However, I can't retrieve the binary file from a Sharepoint list directly. Using the Sharepoint.Tables Data Connector let me navigate to the list and pull metadata from the [Image] column, like this:

{"fileName":"Newfoundland and Labrador.png","serverUrl":"https://<mytenant>.sharepoint.com","serverRelativeUrl":"/sites/<mysite>/SiteAssets/Lists/aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee/Newfoundland%20and%20Labrador.png","id":"eeeeeeee-dddd-cccc-bbbb-aaaaaaaaaaaaaa"}

It is possible to retrieve the list files by filtering the SiteAssets folder contents by matching the Attribute[Path] and Attribute[Name] values to the list's fileName & serverRelativeUrl (without the Url.Encoded filename) fields. However, that requires invoking the Sharepoint.Folder Data Connector function for each file and matching long strings. Function.ScalarVector can batch web calls - could I use that with Sharepoint REST API calls to retrieve binaries more efficiently? This would be the api call?
https://<mytenant>sharepoint.com/sites/<mysite>/_api/web/getfilebyserverrelativeurl('/sites/<mysite>/SiteAssets/Lists/aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee/Newfoundland%20and%20Labrador.png')/$value

That's a tricky approach, if it works at all, and I don't know enough about how to efficiently query Sharepoint. Is there a better way to do this?

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You can use the Sharepoint.Files connector to retrieve the binary data of files in a SharePoint list, in addition to their metadata. This connector allows you to search for files in a SharePoint site or library by specifying a folder path, a file name, or both.

To retrieve the binary data of a file in a SharePoint list, you can use the Binary function and pass it the URL of the file as an argument. For example:

Copy code

Binary(

"<mytenant>.sharepoint.com

)

This function will return the binary data of the file as a table with a single column Binary. You can then use this table in a Table.AddColumn or Table.TransformColumns function to add the binary data as a new column to your list of files.

You can also use the Sharepoint.Files connector to search for files in a SharePoint list by specifying a folder path and a file name. For example:

Copy code

Sharepoint.Files(

"<mytenant>.sharepoint.com

[ApiVersion = 15]

)

This will return a table with a list of all the files in the specified folder. You can then use a Table.Filter function to filter this table to only include files with a specific file name.

Finally, you can use the Table.AddColumn or Table.TransformColumns function to add the binary data of the filtered files to the table, using the Binary function as described above.

I hope this helps! Let me know if you have any questions or need further assistance.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.