Monthly Archives:June 2019

<
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

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.

ByHariharan Rajendran

DAX – Daily vs Weekly vs Monthly Measures

This blog post explains, how to use Daily, Weekly & Monthly measures in single chart or table in Power BI. This might be familiar to you, but I keep getting this question about this.

If you want to build a chart only daily or weekly measures then it pretty easy as you can use the date dimension column and take a measure and it will show the daily result when you have proper relationship between the fact and dim – date tables.

When we want to compare daily vs weekly vs monthly, then we end up with using all the measures in single date context. We need to play with CALCULATE to overwrite the filter context for weekly and monthly measures.

First, lets see how to build individual measures. As I said, it is very simple.

I am taking below table for example.

      
Daily Count – Simply take date and count column and create a chart or table in Power BI.

Weekly Count – Simply take Week and count column and create a chart or table in Power BI.

Monthly Count – Simply take monthly and count column and create a chart or table in Power BI.

Now, let us try to merge them all together to achieve like below.

Let me explain to create the measures,
1. Daily Count
2. Weekly Count
3. Monthly Count.
I have created following columns – Year Week & Year Month as we need to create logic to support even for multiple years.
Year Week = Year & Week — 202014
Year Month = Year & Month — 20203

Please use the proper logic to get the correct digits. I left as simple just for demo. In a real-time, you need to handle month single and two digits. Same for week.

Daily Count – No need to build any measure, we can just directly use Date and Count column.
Weekly Count – As we are keeping date column, we need to override the context to get the sum of daily count for that specific week.

Weekly Count = CALCULATE(SUM(TD[Count]),FILTER(all(TD),TD[Year Week] = Max(TD[Year Week])))
TD – is my table name
I have used Year week column.

Monthly Count – this is same as weekly count. Use Year Month column.
Monthly Count = CALCULATE(SUM(TD[Count]),FILTER(all(TD),TD[Year Month] = Max(TD[Year Month])))

Finally, we can combine all the above measures into single visual.

Hope you like this approach. Please share if you find any other alternate options.

ByHariharan Rajendran

Power BI Report Subscription as PDF

This post explains you how to get the Power BI report subscription as a PDF format. Yes, this will be an interesting solution because Power BI doesn’t send subscription report in pdf/pptx formats. It is always image file as of March 2020. We may expect pdf/pptx formats in future updates.

There are many paid 3rd party applications are available to get the report in pdf formats but this post explains you the process or automation which converts the report easily with using your Power Automate license. Most of the organization are using O365/M365 for Power BI subscription. Those are still getting Power Automate licenses are free. Please check with your M365 admins.

Let me explain the scenario,

I have a report called “IT Spend Analysis Sample” in my workspace and I set up the subscription for all the pages of the report. As you know, it will trigger an email for each report page. I need to get a pdf with all the reports pages (images).

Challenges:

As we get separate emails for each report page, we need to collect all the images and combined them into a single file and get as pdf format.

I am using Power Automate for this whole process. This solution is using SharePoint folder to place the images and final pdf file but this is also possible with OneDrive & Local Folder or any other storage that Power Automate supports.

Steps

  1. Get all the image files from Email and place it on the SharePoint folder.
  2. Image to html conversion and place the html file on SharePoint folder.
  3. You can open and print the html file and save it as pdf file – This is a manual process which you need to perform as we have some issue with the size of the image on the pdf.

 

Step by Step

  1. I have created one Power Automate flow which extracts the image from outlook and place it on the SharePoint folder. There is a Power Automate template available for this process already so we can use this easily.

 

  1. Next, the main process – convert the images into single html file. Follow the below steps in Power Automate.
    1. Start with Recurrence Trigger or manual trigger based on your requirement. Set the time when you need to get the file place on the SharePoint. If you set 7 pm as Subscription time on Power BI report then try to set 7.15 pm on the Power Automate flow. because the other file should get the image from mails and place it on the SharePoint folder.
    2. Use “Get files (properties only)” action and point to the Power BI report images as like below.
    3. Initialize 3 variables – FilePath, JPG & Report.

            4. Next, “Apply to each” condition.

5. Set variable FilePath as “Filename with Extension”.

6. Next, “Get file content using path” action. Check the value below.

7. Set variable JPG as – dataUri(body(‘FileContent’))

8. Next, Append to variable Report – concat(‘<img src=”‘,variables(‘JPG’),'”/>’)

9. All the above processes are within apply to each loop and next add an action after apply to each. Create file in              SharePoint with html as file extension. Use variable report as file content.

That’s it. Now, run the flow it will store the pdf file on the given folder as like below.

Open the html and check the result. It will have all the images as one by one which is nothing but the report pages.

You can print the html and save as pdf. It is a manual process.

Added the screenshot of full flow. Please check it and let me know if you have any doubts.

One more information, Microsoft Power BI team has released new REST API to easy this process. I will cover that in my next post so stay tuned.

Happy Learning!!!