[ #PowerBI ] Quick tip: How to share from SharePoint libraries

This is a probably the most common request among customers, they want to create a dataset from an Excel file stored in a SharePoint library.

In Power BI Service,

the way to do that is easy and straightforward:

You go to Datasets image
You choose import a file image
And then you can choose either OneDrive or a SharePoint – Team Sites image

If you choose OneDrive – Business, a list of folders and files in your own OneDrive will be provided:

image

If you choose SharePoint – Team sites, you will need to provide the URL of the site where the Document Library is, or the URL of Document Library itself

image

Then you will see a list of spaces available at you root site:

image

For any other site than the top level of your tenant, you will need to provide the URL of the site: https://<your tennt>.sharepoint.com/<site> to see the list of Document libraries:

image

and then by clicking on a line:

image

Connect propose two options:

image

You can then either import Excel data into Power BI and it will be a dataset, or you connect and view your Excel in Power BI and become directly a Report.

Please note that you can’t use the URL to someone else OneDrive, it will produce an error like that:

image

And in that matter the “OneDrive” storage space of an Office 365 group is considered as a SharePoint library which it is in fact! 😉 So you can easily use the URL of the group doclib and connect it to Power BI.

In Power BI Desktop,

Question comes when you want to do that in Power BI Desktop. It appears quickly that among the great number of data sources available there is no such thing as a SharePoint Library (there is only here SharePoint list)

image

image

So and this is the tip actually, to use a SharePoint DocLib as source, you should specify the Web data source:

image

And then specify the exact URL of the file. It should be something like :

https://<Your tenant>.sharepoint.com/<sites>/Documents%20partages/My%20file.xlsx

and any other try will give an error)

image

Provide your connection info’s:

image

image

 

and then you arrive at a view showing different structures inside the file for example the arrays and the worksheets :

image

After loading and processing, your data are in PowerBI Smile

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s