BLOGS

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.

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.