Tag Archives: SharePoint folder as a source

ByHariharan Rajendran

On-Prem SharePoint Folder as a Source in Power BI – Part 2 SharePoint.Contents

Check my previous blog post where I have explained how to connect SharePoint On-Prem folder as a source to access excel or csv files using SharePoint.files function in Power BI Desktop.

On-Prem SharePoint Folder as a Source in Power BI

This article explains how to use SharePoint.Contents function in Power BI to achieve the same function. It is kind of Part 2 version of my previous blog post.

Why you need to care about SharePoint.Contents?

I have used SharePoint.Files on some of my clients to access there excel files in Power BI Desktop, it is working good for few of them but not for others. I got the below error message.

 

I have tried different option to fix the issue, but it was not working so I tried with SharePoint.Contents and it worked well. I may suggest you use SharePoint.Contents first when you access SharePoint files.

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.

let

Source = SharePoint.Contents(“http://<siteurl>.com/sites/<sitename

in

Source

Step 5 – Copy and paste the above script in advanced editor window and click done.

Step 6 – It will load all the objects from SharePoint site as like below.

Step 7 – Check for your folder name under Name column and click Table under content column. For example, if my folder name is “Power BI Files” where I have all my excel files are there.. Click the Table and it will expand all the sub folders and then if we click Table again then it will show the list of files from the folder as like below. We need to navigate to multiple levels based on the folder structure.

Step 8 – Filter for .xlsx under Extension column, if you have other documents also placed on the same folder.

Step 9 – Rest of the steps are same as discusses in my Part 1.

The above method is works well when you have multiple files in a folder. It will use the automatically created parameter and function.

What if you want to access only one excel file from the folder? Can I use the same method? Yes. You can use the same method and you can filter only that specific file and follow the rest of the steps.

Sometimes you think that parameters and functions are not required when you just access one single. Yes, you are correct. Use the below Approach to access the single file which will avoid creating and using parameter and 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.

let

Source = SharePoint.Contents(“http://<siteurl>.com/sites/<sitename>/”)

in

Source

Step 5 – Copy and paste the above script in advanced editor window and click done.

Step 6 – It will load all the objects from SharePoint site as like below.

Step 7 – Check for your folder name under Name column and click Table under content column. For example, if my folder name is “Power BI Files” where I have all my excel files are there.. Click the Table and it will expand all the sub folders and then if we click Table again then it will show the list of files from the folder as like below. We need to navigate to multiple levels based on the folder structure.

Step 8 – Get the below details.

  • Excel file Name
  • Excel Sheet Name

Step 9 – Open Advanced Editor and you could see few lines of script which you performed above.

Step 10 – Remove the “in” section and add comma (“,”) at the last line of the script then add the below lines. Replace the below red highlighted text into your previous step name. Your step name may be like in the following format #”Path”. Just copy and paste it below script.

#”Filtered Rows” = Table.SelectRows(PreviousStepname, each ([Name] = “YourExcelFileName.xlsx”)),

Navigation1 = #”Filtered Rows”{0}[Content],

Excel = Excel.Workbook(Navigation1 , null, true),

Completions_Sheet = Excel{[Item=”YourExcelSheetName”,Kind=”Sheet”]}[Data],

#”Promoted Headers” = Table.PromoteHeaders(Completions_Sheet, [PromoteAllScalars=true])

in

#”Promoted Headers”

Step 11 – Your final script will look like below. You may have extra lines at the top based on your folder path level.

Let me know if you have any doubts. Happy Learning!!!

ByHariharan Rajendran

On-Prem SharePoint Folder as a Source in Power BI

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.Contents
  • SharePoint.Files
  • SharePoint.Tables

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.

let

    Source = SharePoint.Files(“http://<siteurl>.com/sites/<sitename>/”)

in

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.

1