Tag Archives: Power BI

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.


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



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.


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



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])


#”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.


    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.

ByHariharan Rajendran

Automate on-demand Dataset Refresh in Power BI – Self Service BI

This blog post explains how to perform ad-hoc datasets refresh automatically in self-service Power BI model-based organization. Most of the time, Administrators may look for an option to automate this requirement, so this post helps admins who wants to automate the ad-hoc datasets refresh request.

Most of you know about how the self-service BI model works but still I need to explain to make the context clear. In self-Service BI models, administrators will setup the Power BI environment and on-board the users to the power bi platform. In detail, administrators will create workspaces for users and apply security and governance to manage and maintain the environment.

Users will have contributor access to publish the report to their Dev or UAT workspaces. Administrators will help the users to migrate the reports to PROD workspace. Users will not have access to PROD workspace most of the time. User / Business Users will have access to PROD workspace app to see their reports.

In this scenario, Users can ask admin to setup the schedule refresh and datasets will refresh on the set schedule time. In case, if user want to refresh their PROD datasets on-demand or ad-hoc basis then user should request admin to refresh the dataset may be via email.

If admin is available, then manual refresh can happen quickly but if admin is not available or support team is not available for sometime then user should wait until admin refresh the datasets or next schedule refresh time. The turn around time of the request will be longer than expected most of the time. This also another workload to admins to refresh the dataset manually for every user who are requesting to refresh their datasets.

How to automate this ad-hoc refresh? This is will be question for most of the administrators.

This post gives solution with the help of SharePoint and Power Automate.

SharePoint – You can use SharePoint online or on-prem with Power Automate. Power Automate has a default Power BI connector to refresh the datasets by passing the workspace name or id and datasets name or id.

SharePoint Side Steps

You need to create a SharePoint list with below columns.

  1. Power BI Workspace ID
  2. Power BI Dataset ID

It will be difficult to users to enter their PROD workspace ID and dataset ID. They will not have access to their PROD workspace, so admin has to share their workspace and dataset id. As I said, this is automated solution, so this step will not effective way to manage.

But the above are mandatory to pass into Power Automate. To make the process easy to users, admin can create three SharePoint lists.

  1. Power BI Workspace
  2. Power BI Datasets
  3. Power BI Ad-hoc Dataset Refresh – Final

The first 2 lists dependent lists for the 3rd Final List.

Power BI Workspace SharePoint List

This list should have below columns.

  1. Workspace Name
  2. Workspace ID

Enter all the PROD workspace name and ID. Again, this can be automated with the help of Power BI REST API and Power Automate.

Power BI Datasets

This list should have below columns.

  1. Dataset Name
  2. Workspace Name – Lookup column from Power BI Workspace SP list
  3. Dataset ID

Choose the workspace from the drop down and enter the dataset name and ID.

Power BI Ad-hoc Dataset Refresh – Final

This is SharePoint list where you need to provide access to users. This list will have below columns

  1. Workspace Name – Lookup
  2. Dataset Name – Lookup
  3. Workspace ID – Hidden
  4. Dataset ID – Hidden
  5. Status – Default value as “Not Triggered”

I have applied the cascading list option and built the SharePoint list to get the above look and feel (drop-downs). This is possible with the help of script editor webpart.

User can choose their workspace and dataset from the drop-down. Also, admin can enable the security in SharePoint to see only own user entries.

Power Automate

We need to setup the trigger and actions to run the workflow in Power Automate.

Trigger will be from SharePoint – When an item is created in SharePoint list.

Action – it  will be Power BI Dataset Refresh.

By default it will show the list of workspace from your Power BI tenant but here it should be dynamic so you need to choose “Enter custom value” and pass the SharePoint list – Workspace ID value and same on Dataset – Dataset ID Value.

Finally, update the SharePoint list column – Status into “Successfully Triggered”.

That is, it. When user add their entry in SharePoint List then Power Automate will trigger the dataset refresh and update the SharePoint list as “Successfully Triggered”.

The full model screen

Next Step

We still can automate further like send email to users when the status changed on the SharePoint list and we may not sure whether the datasets is successfully triggered or not so we can use Power BI REST API to get the last refresh status and update the same to partner about the status of the dataset refresh.

Share your comments below.

Happy Learning!!!

ByHariharan Rajendran

Power BI REST API in PowerShell with No User Credentials pop up

In this post, I am explaining how to run the Power BI REST API based PowerShell scripts without getting a credentials pop-up window.
If you want to schedule a PowerShell file then it should not show pop-up then only it will work on windows job scheduler.
If you see the below screen, I was trying to run the PowerShell scripts and got pop-up because I used only Client ID and not passed the Username and Password.

I used the below function.
function GetAuthToken


if(-not (Get-Module AzureRm.Profile)) {
Import-Module AzureRm.Profile
$redirectUri = “urn:ietf:wg:oauth:2.0:oob”
$resourceAppIdURI = “https://analysis.windows.net/powerbi/api”
$authority = “https://login.microsoftonline.com/common/oauth2/authorize”;
$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $authority
$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, “Auto”)
return $authResult

Let me show how to run the script without getting the credentials pop-up window.
We need to modify the above GetAuthToken with below script.
function GetAuthToken

$username = “youremailaddress”
$password = ConvertTo-SecureString “password!” –asplaintext –force

if(-not (Get-Module AzureRm.Profile)) {
Import-Module AzureRm.Profile
$redirectUri = “urn:ietf:wg:oauth:2.0:oob”
$resourceAppIdURI = “https://analysis.windows.net/powerbi/api”
$credentials = New-Object System.Management.Automation.PSCredential $Username,$password
$AADcredential = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList $credentials.UserName,$credentials.Password
$authority = “https://login.microsoftonline.com/common/oauth2/authorize”;
$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $authority
$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId,$AADcredential)
return $authResult

In the above script, I have introduced 2 variables $username and $password and hard-coded the credentials on the script itself.
Again another set of variables to handle the username and password $Credentials & $AADCredentials.
Use the above code on your script and it will not ask pop-up any window. You can use the PowerShell script file on the job scheduler.

Happy Coding!!!

ByHariharan Rajendran

Power BI Bookmark and Selection

Microsoft Power BI recently released new features as part of October release. In that, the below two features are very important to consider for our reporting solutions.

  1. Bookmarking
  2. Selection Pane.

First, let me explain the selection pane feature. It is useful to show and hide any report elements in the report.

If we see selection pane alone, then we can’t identify the importance of that but, this selection pane will combine with the bookmark and do magic.

Please check the below report and play with different chart types.


Take your time and think that how this report works. Actually, it is a simple when you see the report but in the backend it using the selection and bookmark features.

Before explaining how the report is built, let me explain what bookmark feature is.


It is a preview feature which means it is not generally available. Microsoft Power BI team announced this feature during Data Insight Summit. They have created a hype to the feature.

We can create a bookmark for the interesting stats. For example, if you want to create a data story or you want to present the report to the business users to show the sales and revenue information.

At that time, you may need to show metrics for last year sales and this year sales for comparison and same for revenue or you may need to highlight a specific visual (it can be done with spotlight, refer here).

With the single report you need to filter the data during the presentation but instead, you can create a bookmark for each one of the results and can show them easily.

Learn more about Bookmarking feature in Power BI site.

Let me explain how the report is built with the help of bookmark and selection pane. Follow the below steps to reproduce the report.

  1. Create a report with pie charts
    1. Create a bookmark as pie
  2. Add the donut charts on the same page where pie charts are placed
    1. Create a bookmark as Donut
  3. Add column charts on the same page where pie and donut charts are placed
    1. Create a bookmark as Column
  4. Open the selection pane and hide the donut and column charts and update the pie bookmark
  5. Choose Donut bookmark and hide pie and column charts and update the bookmark
  6. Same for column bookmark
  7. Add the slicer on top and added three images (pie, donut & column chart icons)
  8. On each image, on Link option choose bookmark type and select the relevant bookmarks

That’s it. Please let me know your comments below also share if you have any other report logics with these features.

ByHariharan Rajendran

Power BI Spotlight

It is one of the features recently released by Microsoft Power BI Team.

Actually it a very simple feature but more effective. You can check the spotlight for any visuals or elements that you added in your report.

You can see this option when you click “…” (Three dots) on any visual’s top right side.

It enables you to highlight only the specific report element from your report. During the presentation, if you want to highlight any visuals or elements then spotlight can be used.

It also can be added as a separate bookmark. Refer bookmark feature in Power BI.


ByHariharan Rajendran

Explore Power BI Report Server

Microsoft Power BI team releases a Power BI Report as generally available. We can get this report server as part of Power BI premium but still, we can use the trial version for 180 days.

This post explains to you that how we can setup the Power Bi report server. You can download the Power Bi Report Server here.


It contains two components

  1. PBIReportServer.exe
  2. PBIDestopRS (32 & 64 bit)


It is easy to install Power BI Report server in your system. Just follow the below screenshots and click Next and complete the installation.

Once the installation is completed, we need to configure the Report Server which is nothing but our regular SSRS report server configuration but for Power BI.

Configure as usual and test the web service and web portal URL.

To start developing the reports, need to install the Power BI Desktop RS which is same like regular Power Bi desktop but with few changes.

We can save the report in our on-premises portal with the PBIDesktop RS tool.

Check the general information about Power BI Report Server.

Report Server Power BI Report Server
Instance ID PBIRS
Product Version 14.0.600.286
Service Power BI Report Server
Log on Account NT SERVICE\PowerBIReportServer
ByHariharan Rajendran

R Packages in Power BI

Power BI service supporting many R packages as of now and it will increase in future. There is a misunderstanding that we can use these packages in Power BI Desktop R visual.

We can’t use all the packages in the Power BI site because it includes the packages which can be used for R custom visuals development.

Check the list of packages that Power BI Service supports, here.

Let us take an example,

There is a package called “timevis” which is part of Power BI supported packages.

We can use this package to build a visual to display a time range information. I used this package in R studio and got the below visual.


You can use the below R script and try it by yourself. This package requires many dependent packages which you can identify while installing the R scripts. If any dependent packages are missing then you should install those missing packages and finally run the R script.


input<- data.frame(

           id = 1:4,

      content = c(“Apple”, “Orange”, “Mango”, “Banana”),

        start = c(“2017-01-10”, “2017-01-11”, “2017-01-20”, “2017-02-14 15:00:00”),

          end = c(“2017-02-13”, NA, “2017-02-04”, NA)



It is easy to identify which packages are supported in Power BI from the list. Prepare your R script from any of the packages and make sure to check the supported package list and run the script in R studio.

If your package producing a visual and make sure that visual is displayed under plot section of R studio. If you see the visuals in plot section then those packages can be used in Power BI.

In our example, the visual is displayed under viewer section which means it is using HTML viewer which is not yet supported by R visual in Power BI.