This blog post explains you the problems and solution with SharePoint on-prem source in Power BI Desktop.
The list of connectors in Power BI Desktop is keep increasing every month. Thanks to Power BI team who working hard to integrate new connectors with Power BI desktop.
Let me explain you the problem or issues that I see every time in forums and community questions.
Problem– Not able to access excel or csv files from On-Prem SharePoint folder. How to access it?
Yes, the question is simple and you may think that we have different methods to access it. Let me specify the methods below and issues.
SharePoint Folder – We can see SharePoint Folder as a one of the source under Get Data which can be used to connect both online and on-prem SharePoint but sometime it is not working properly with on-Prem SharePoint folders.
Have you tried it? If not then try. You may get this below error.
Error – “We encountered an error while trying to connect. Details: “We found extra characters at the end of JSON input'”
Folder – UNC method – This is very good workaround to connect with SharePoint folders. We need take the SharePoint folder Web URL and convert them into local folder type URL by changing the slash (“\”). This works very well in my case. If you are user and you can access your SharePoint folder as windows explorer folder. As you know, you need to choose Folder as a connection type while configuring data source in gateway. It will work when you use your account for configuring the credentials.
In real-time, we will not use individual account on the gateway data source and it will be a service account or the windows account where the gateway clusters are installed. I have received an error when I configure a folder data source with gateway machine account credentials.
Proper access was given to the gateway account but still got the above error message. This might be working for you. If yes, leave your comment below.
Web – If you have single file stored in SharePoint folder then get the file URL and use Web as a source and connect with the excel file. It is very good option when you have single file. How to handle multiple files?
I have tested with multiple clients and above methods are not working because their on-prem SharePoint servers are not a latest version and few other reasons.
How to solve this issue?
I mean how to access the on-prem SharePoint folder files from Power BI Desktop? Solution is simple. Don’t use GUI for this scenario. Use M query function in blank query.
We have below function in Power BI related to SharePoint.
SharePoint.Tables is specifically for SharePoint list. We have SharePoint Online List and SharePoint List as two separate connectors. Use one of them and connect with your SharePoint list and check the advanced editor then you will see the SharePoint.Tables function.
Let us come to the topic now. What are the other 2 SharePoint functions and what is the use of those functions and how those function will solve the on-prem SharePoint folder issues?
I will answer for all the questions below.
Let us start with SharePoint.Files function.
Follow the below steps.
Step 1 – Get your SharePoint Site URL where you have your folder.
Step 2 – Go to “Get data” and choose Blank Query.
Step 3 – Click “Advanced Editor”
Step 4 – Get your SharePoint site name and replace it in below script.
Source = SharePoint.Files(“http://<siteurl>.com/sites/<sitename>/”)
Step 5 – Copy and paste the above script in advanced editor window and click done.
Step 6 – This will list out all the files from SharePoint site.
Step 7 – You can see the last column folder path and filter your path where you have all your excel files and filter .xlsx and .xls on the extension column based on your file extension.
Step 8 – Choose Content column and right click and remove other columns.
Step 9 – Expand the content column and it will take some time to open your excel file and sheet.
Step 10 – It will create a group with sample file and parameters and the same will be used in the query as like below.
Step 11 – Finally, the table will be loaded with all the columns and you can use it and perform further transformations as per the requirement.
You also can create function and invoke them instead of using automatically created function and parameter.
I will cover the SharePoint.Contents in my next article. Thanks. Happy Learning.