BLOGS

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.

ByHariharan Rajendran

Will Power BI recognize Defined Names in Excel?

Most of you familiar with accessing excel files from Power BI Desktop. Let me as a below question.

Will Power BI recognize “Defined Names” from Excel?

If you take excel expert who use most of the features from excel for analysis, they may create multiple sheets, tables (range of cells) & defined names (range of cells). They may be interested to see all the objects in Power BI. Will Power BI help excel experts to access all the objects?

The above two questions are same but slightly different in context. Yes, is the answer for above two questions.

Let me explain Define Name in Excel first.

Excel – Define Name

Like table, we can define a name for range of cells. It is kind of subset of data from the whole sheet data.

You can find the “Define Name” under Formulas tab.

Select the Define Name, it will ask you to enter the name and range of cells in refer section as like below.

I have defined a name called “Profit” with L column. This will be smaller table with Profit as a column within the table.

You can define multiple names, and all can be referred in your excel calculations. You can check the list of names you defined as like below.

Now, you got a basic idea of Define Name in excel. Refer excel articles to understand more about this feature.

Let us come to our Question again. I said Yes, Power BI will recognize these names. Let me show you.

Power BI

If I connect this excel in Power BI Desktop, you can below objects under Navigation.

  1. Sheets
  2. Tables
  3. Defined Name – as table

In the above screenshot, Country, Sale_Price & Sales are defined names.

All the above defined names are recognized as tables in Power BI. We can use them for report.

Performance

You may get the following question; will it be any performance issue when we use these defined names?

It depends on the scenario. Consider you have a excel with more number of columns and you have defined a name with few columns and measures. In this case, you can consider only the defined name and no need to use the full sheet in Power BI. This will improve the performance.

Try to avoid using the whole sheet and defined names together because defined name is already available in whole sheet and Power BI refresh will take time to refresh as it needs to refresh the whole sheet and defined name.

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

Migrate RDL reports to Power BI – RDL Migration Tool

This article explains how to use the RDL Migration tool. This tool helps you to migrate your SSRS reports to Power BI paginated reports and also SharePoint base SSRS to Power BI.

We already have very detailed github document for RDL migration. This article highlights some of the points which you need to remember before performing RDL migration.

  1. You need to have Power BI Premium with Paginated Reports workload enabled.
  2. Download the RDL Migration Tool from Github – https://github.com/microsoft/RdlMigration

RDL Migration tool is a zip file which contains a visual studio solution. Open the visual studio solution which will have the below structure. It has RDL Migration and Unit Test projects.

If you debug the project, then you will get an error message. Follow the below step to resolve the error.

Change the Web Reference URL of “ReportServerApi” with your report server web reference URL.

The existing value is – http://ericpbi/ReportServer/ReportService2010.asmx?wsdl

Change ericpbi into your report server name and test the URL in your browser and it should open a proper page.

You may get an error with IReportingService2010 in RdlFileIO.cs file. Change IReportingService2010 to ReportingService2010 wherever available.

That’s it then as per the GitHub document run the RdlMigration.exe and pass the parameters.

Happy Learning.

ByHariharan Rajendran

How to split by delimiter in Direct Query Power BI Reports?

This article explains you about how to achieve the split function in Direct Query Power BI reports. As you know, we can achieve the split function either in Power Query layer or in DAX but we don’t have a direction function called split in DAX but still we can some other functions which will help us to achieve the result.

Let’s take the below example.

EmpDetail column has Name, Email, Phone number & Department values but are delimited by “|”. We need to split them all and create separate columns.

Split in Power Query

Use “Each occurrence of the delimiter when you have multiple delimiter in your column. It will create multiple columns based on the number of delimiters.

Split function in DAX

We don’t have direct split function in DAX but we have different approaches to achieve the result but we need to create multiple columns as each DAX function is applies to one column.

We can use below functions to achieve the split.

  1. LEN
  2. LEFT
  3. RIGHT
  4. MID
  5. SUBSTITUTE
  6. TRIM

We also can use PATH functions to get the result. Refer below.

Name = PATHITEM(EmpDetails[EmpDetails],1)

Email = PATHITEM(EmpDetails[EmpDetails],2)

Phone = PATHITEM(EmpDetails[EmpDetails],3)

Department = PATHITEM(EmpDetails[EmpDetails],4)

But all the above will work without an issue when we have Import mode connectivity.

What will happen if we use Direct Query?

Let us first try with Power Query first,

If I use Split by column then we will end up with below message.

“This step results in a query that is not supported in DirectQuery mode.”

It means, as we are connecting datasources which are support direct query doesn’t support this Split bu delimiter function so we can’t use this method.

Let us try in DAX layer now. Again, we get same kind of message “Function PATHITEM is not allowed as part of calculated column DAX expressions on DirectQuery models.”

Do we have solution for this type scenario?

Yes. We still can achieve the expected result with the help of other DAX functions which I mentioned above.

Name =

VAR String_Length = LEN(EmpDetails[EmpDetails])

RETURN

TRIM (

        LEFT(

                SUBSTITUTE(EmpDetails[EmpDetails],

                           “|”, REPT(” “,String_Length))

                   ,String_Length

               )

)

The above DAX script will give the name value from the EmpDetail column. We can do the same to split the other values.

Let me explain the above script to understand better.

I have created a variable to store the Length of the whole value. You still can create a new column for length and can be used in this script.

SUBSTITUTE section

SUBSTITUTE(EmpDetails[EmpDetails], “|”, REPT(” “,String_Length))

We are changing the “|” value into spaces. REPT is used to repeat the spaces with length size. The result will be like below.

There are total length spaces between each word.

LEFT Section

LEFT(

          SUBSTITUTE(EmpDetails[EmpDetails],

          “|”, REPT(” “,String_Length))

    ,String_Length

)

Lets assume the length of the whole value is 35, Left function will show the 35 letters from the left position so we will get as like below.

It has extra spaces at the end of each value.

TRIM section

TRIM (

           LEFT(

                     SUBSTITUTE(EmpDetails[EmpDetails],

                   “|”, REPT(” “,String_Length))

          ,String_Length

            )

)

TRIM will remove the extra spaces and show only the proper value. Now, we will get only the Names.

Like the above we need to do the same for other columns, but we can’t use the same formula. We need to modify slightly.

Email =

VAR String_Length = LEN(EmpDetails[EmpDetails])

RETURN

TRIM (

           LEFT(

                   RIGHT(

                                 SUBSTITUTE(EmpDetails[EmpDetails],

                               “|”, REPT(” “,String_Length))

                  ,String_Length*3

                          )

         ,String_Length

         )

)

You need to use RIGHT function and multiply by 3 with the length then use the LEFT function.

Phone =

VAR String_Length = LEN(EmpDetails[EmpDetails])

RETURN

TRIM (

             LEFT(

                        RIGHT(

                                       SUBSTITUTE(EmpDetails[EmpDetails],

                                     “|”, REPT(” “,String_Length))

                    ,String_Length*2

                            )

           ,String_Length

                )

)

Department =

VAR String_Length = LEN(EmpDetails[EmpDetails])

RETURN

TRIM (

    RIGHT(

            SUBSTITUTE(EmpDetails[EmpDetails],

           “|”, REPT(” “,String_Length))

    ,String_Length

            )

)

Result is as like below.

First and Last columns will be easy. If you are working on 2nd column then you need to multiply by “Total columns -1”.

3rd Column “Total columns -2” and so on.

Still there could be other ways. Please share your thoughts.

Happy scripting.

ByHariharan Rajendran

[Solved] Power BI Dataset Refresh Error – The key didn’t match any rows in the table

This week I got an error on my dataset schedule refresh which I was not able to solve and the error message also weird as I have not encountered this before. Finally, I have fixed the error with the help of Power BI support team.
When I get the error message and I have checked the report and tried to fix it but no luck. I googled this error message and found one result in Microsoft community forum. Around 2 users got the same error message last month and no solution was discussed so I thought it would be helpful to everyone if I post a solution.

This is the reason behind of this article.

Scenario

I have created a dataflow for one table and used that table in my Power BI report with other SQL Server tables. When I schedule a report refresh, I got a below error message.
{“error”:{“code”:”ModelRefresh_ShortMessage_ProcessingError”,”pbi.error”:{“code”:”ModelRefresh_ShortMessage_ProcessingError”,”parameters”:{},”details”:[{“code”:”Message”,”detail”:{“type”:1,”value”:”The key didn’t match any rows in the table.”}}],”exceptionCulprit”:1}}} Table: Product.
I have tried all the below steps as part of investigation.
1. Checked the report in Power BI Desktop and refreshed it. It works well.
2. Refreshed the dataflow in service, again it refreshed successfully.
3. Both on-demand & schedule refresh of dataset throws same error message.

Solution

I have reached Power BI support team and fixed the issue.
1. They first started with dataflow. Asked me to refresh the dataflow.
2. Then asked me to create a report using only dataflow.
3. Again, new report with only SQL server table without dataflow. All the steps are worked well.
4. Checked the gateway connections.

Finally, they asked to re-enter the datasource credentials again. This fixed the issue.

Yes. Re-enter the datasource credentials which will fix the error. Try this solution and fix your error.

ByHariharan Rajendran

Look Like Custom Visuals from Pre-Built Visuals in Power BI

This article explains that how to get different visuals from our pre-built visuals in Power BI. I have used these visuals on my project so I thought this would help other when you have same kind of scenarios.

Visual 1 – Customized Column Chart

My business user was using excel for their reporting and recently moved to Power BI. They were looking for a visual which is supported only in excel but they want to get in in Power BI.

Solution

As we know, we do not have any pre-built visual as like above, but we can get by combining 2 different visuals with overlap. Let me share how I achieved this visual.

Let us take a sample data for this visual. I am using financial sample data and these visuals are using Count as an aggregation type as I want to show the result with small number.

First build a clustered column chart as like below using following columns Segment, Country & Count of Records.

Next drag and drop another clustered column chart and use the following columns Segment & Count of Records. Note – Do not use Country on legend.

Format Settings

  • The size of the chart should be same as previous chart.
  • Remove background for both the charts.
  • Change the color of title, x and y axis names to white.
  • Set Y Axis scale type is Linear and Start value is 0 and End Value is 40. Based on your maximum count, set the value. Apply the same to both the charts.
  • The second visual will look like below.

Now, we need overlap on the same position and set the 2nd visual to back which means the 1st chart with country will be in front position.

Select both the visual and group them. Finally, you will see the result like below.

 

Visual 2 – Custom Donut & Pie Charts.

Same as previous approach, we are going to get below visuals.

I have used 3 charts and grouped them to make it as single chart.

Place them all with some space and group it. Like these 2 visuals, you can play with other visuals and get a new visual. Share your comment below.

Download the PBIX from here.

ByHariharan Rajendran

Power BI Export Report into PDF – REST API Support

This article explains a most wanted solution in most of the organizations. Yes, it is talking about exporting a Power BI report into pdf, ppt and png. You may think that these options are already available and why I have taken this topic. Yes, we can export a report into pdf or ppt but we manually need to go to report and choose the export option to get the report in either pdf or ppt.

I will tell you how to automate this process, I mean you no need to open the report manually and export it. We also have subscription option which does the same but not in an effective way. I would say still (when am writing this article). Power BI does not have a proper subscription feature. There are lot of limitations. Check Power BI Subscription in Microsoft documentation.

This article refers the Power BI REST API which release by Power BI team recently for exporting the report into pdf, ppt and png. Refer – https://powerbi.microsoft.com/en-us/blog/export-report-to-pdf-pptx-and-png-files-using-power-bi-rest-api/

I would say this is fantastic update from Power BI team which enables us to perform so many options and it will be the alternative for proper subscription feature in Power BI.

Use Cases

  1. Build your own custom subscriptions
  2. Enable users to download their report into pdf whenever they need without opening the report.
What are those REST APIs?

We have 2 to 3 REST APIs to support following actions.

Export the file – https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/ExportTo

Check the Status – https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/exports/{exportId}

Download the file – https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/exports/{exportId}/file

The above all REST APIs are support in both My Workspace and App workspace but the workspace should reside in Power BI Premium Capacity.

The Following article explains how to use these REST APIs in C# code – https://docs.microsoft.com/en-us/power-bi/developer/embedded/export-to

Let me explain, how to use PowerShell script to perform export the file and download the file.

What you need to test?
  1. Register an Azure APP and get the Client ID – https://dev.powerbi.com/apps
  2. Power BI Premium Capacity to your workspace – You can use your Power BI Premium Capacity or Azure Power BI Embedded Capacity.
  3. Sample report published into your workspace
  4. Get Workspace & Report ID

Open your Power BI report in browser and get the id.

In the above image, id after groups is Workspace ID or GroupID and Report ID is available after Reports.

Export a File

Use the below URL,

https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/ExportTo

Replace the $Group_ID and $Report_ID with whatever you got from your report URL.

Export file REST API is a POST method and it also requires Body when we request the URL. I have using pdf format.

Refer the below PowerShell script.

$uri = “https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/ExportTo”

$body = “{`”format`”:`”pdf`”}”

$FileExport = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method POST -body $body

 Get File Status

Above step triggers the export file job, we need to know the status of the export. If the export status is completed means the file is ready to download.

This is a GET method.

Get the Export file id from above PowerShell script and use it in below REST API.

Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET

Use the below PowerShell script.

$id = $FileExport.id

$uri = “https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/Exports/$id”

$FileStatus = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET

Get or Download a file

Again it is using GET method. Following URL is the REST API URL.

https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/Exports/$id/file

Here $id is Export file id which we get from Export file section.

Use the below PowerShell script.

$uri = “https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/Exports/$id/file”

$File = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET -OutFile $Folder+”_new.pdf”

I am using -OutFile option to get the file stored in local folder with filename with extension as .pdf.

Get the full script from my GitHub Repository.

Next article, I will cover the Use cases with this REST API.