BI

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

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!!!

 

ByHariharan Rajendran

Power BI Report Server – Change Data Source using REST APIs

This post gives an idea that you can change the Power BI report’s data source without using Power BI desktop. Do you think is it possible? I am talking about Power BI Report Server.

Yes. This is possible with the help of Power BI Report Server REST APIs. You can go through the supported API methods using the below URL.

https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0

The syntax of the REST API is http://<reportservername>/reports/api/v2.0/

Scenario:

I have a report published into Power BI Report Server portal and I want to change the database used on the report to some other database. In this example, I am using a database called IT_Dev on my report and I will be changing the database to IT_Prod. Even you can change the server name also.

I am using Postman tool to access the REST APIs. Please check my below article to know about using the Postman with Power BI Report Server REST APIs.

Power BI Report Server REST API with Postman

We need to perform 3 main steps.

  1. Get the report ID of which we want to change the data source
  2. Get the Current data source information of the report
  3. Update the data source

Initially the below data source is used on the report.

Get Report ID

Use the below REST API to get the ID of the report. It will show all other information of the report. Since we are using GET method, you can access this URL on the browser itself.

Method – GET

URL – http://localhost/reports/api/v2.0/PowerBIReports

Here PowerBIReports is a REST API which will list out all the reports from report server.

Note down the report ID of the report.

Get Current Data Source Information

As we are trying to update the data source, we need to know the structure of the current data source so that we can use the same structure while updating the data source.

Method – GET

URL – http://localhost/reports/api/v2.0/PowerBIReports(6b20ecf7-54b6-40da-8970-92a8576f665a)/DataSources

We need to use the captured ID on the above URL.

Note down the Value section of the result.

Update the Data Source

In this step, we will update the database from IT_Dev into IT_Prod.

Method – PATCH

URL – http://localhost/reports/api/v2.0/PowerBIReports(6b20ecf7-54b6-40da-8970-92a8576f665a)/DataSources

We are using the same URL but with different methods. And, we need to paste the data source value script which we copied on the previous step on the body section. Change the database name and enter the username and password.

Structure:

[

{

“Id”: “2dc59c3d-e941-ea11-bed7-08edb9949ce0”,

“Name”: null,

“Description”: null,

“Path”: null,

“Type”: “DataSource”,

“Hidden”: false,

“Size”: 0,

“ModifiedBy”: “<domain\username>”,

“ModifiedDate”: “2020-01-31T19:39:14.067+05:30”,

“CreatedBy”: “<domain\username>”,

“CreatedDate”: “2020-01-28T21:44:21.507+05:30”,

“ParentFolderId”: null,

“IsFavorite”: false,

“Roles”: [],

“ContentType”: null,

“Content”: “”,

“IsEnabled”: true,

“ConnectionString”: “data source=.;initial catalog=IT_Dev;persist security info=False”,

“DataSourceType”: null,

“IsOriginalConnectionStringExpressionBased”: false,

“IsConnectionStringOverridden”: false,

“CredentialRetrieval”: “prompt”,

“CredentialsByUser”: null,

“CredentialsInServer”: null,

“IsReference”: false,

“DataSourceSubType”: “DataModel”,

“DataModelDataSource”: {

“Type”: “DirectQuery”,

“Kind”: “SQL”,

“AuthType”: “Integrated”,

“SupportedAuthTypes”: [

“Integrated”,

“Windows”,

“UsernamePassword”

],

“Username”: “<yourusername>”,

“Secret”: “<YourPassword>”,

“ModelConnectionName”: “d0f1b0ce-c7e4-4ff9-b72f-3df06f48f6fe”

}

}

]

That’s it. If you go and check the report data source then you can see IT_Prod.

Video explanation

Please leave your comments below. Happy Learning!!

ByHariharan Rajendran

Min and Max value in Power Query Power BI

Let me ask a simple question.

How to get the minimum value in Power BI? Everyone answers min() which will give minimum value.

This can be achieved in both Power Query and DAX layers.

As you know MIN(<columnname>) measure will be filtered down based on the dimension column that we use. In this post, I am explaining how to achieve the MIN / MAX value in Power Query.

Scenario – I have a below table and I want to show the minimum amount value of A & B also I want to show the original amount.

Current Data

Expected Result

A’s minimum value is 12 and B’s minimum value is 5. I want to show these numbers on A & B as below.

Let me walk through the steps to achieve an expected result.

Step 1:  use Group by ID transformation and choose “All Rows” as operation as below.

Step 2 – Create a new column with the below expression.

Table.Column([Count],”Amount”)

Here “Amount” is the column that was available before Group by. Need to provide that column name on Tabe.Column function and Count is nothing but the new column which we got as a result of the group by.

Step 3 – Either create a new calculated column or update the previous column with an additional expression as below. That expression List.Sort. The result of the step is a list. We need to sort the list in ascending order.

List.Sort(Table.Column([Count],”Amount”))

Step 4: As we are targeting for minimum value, we need to get the first value from the sorted list. Use the List.First function.

List.First(List.Sort(Table.Column([Count],”Amount”)))

The final expression will be like below.

Step 5 – Now, we will get the result as below. Remove the Count column and we will be getting only ID & Custom columns.

Step 6: Name this table and go to the main table and use merge transformation and merge with the new table using the ID column.

Step 7 – Expand the column and we will get the below-expected result.

If you want to get the maximum then use descending on the list.sort then the rest of the steps will be the same.

Happy Learning.

ByHariharan Rajendran

Power BI Report Server REST API with Postman

This post explains how to use Postman tool to access the Power BI Report Server REST API. As you know, Microsoft Power BI team released the REST API feature for both Power BI Service and Report Server. Using this REST API, we can access and control the Power BI objects.

Use the below URL to access Power BI Report Server REST API.

http://<reportservername/reports/api/v2.0/

The list of objects which supported in REST API are,

  • AlertSubscriptions
  • CacheRefreshPlans·
  • CacheRefreshPlanHistory
  • CatalogItems·
  • Comments·
  • DataSets·
  • DataSetData·
  • DataSources·
  • ExcelWorkbooks·
  • Extensions·
  • FavoriteItems·
  • Folders·
  • Kpis·
  • LinkedReports·
  • MobileReports·
  • Notifications·
  • PowerBIReports·
  • Reports·
  • ParameterDefinitions·
  • Resources·
  • Schedules·
  • Subscriptions·
  • SystemResources·
  • SystemResourceItems·
  • UserSettings·
  • System·
  • Me·
  • Telemetry·
  • PowerBIIntegration·
  • ServiceState

Microsoft providing the swagger site to show the detailed REST APIs.

https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/

Example,

If I want to see the list of Power BI reports then we need to use the below URL on the browser. Actually, it is a get method so we can use the browser.

http://hari-pc/reports/api/v2.0/PowerBIReports

It also will show the properties of each report.

If we want to use the other method like POST, PATCH, PUT and DELETE then we need to use any other application because it can’t be done in browsers. Use the Postman tool to access all the REST API methods.

  1. Choose Methods
  2. Place the URL
  3. Authorization
  4. Body
  5. Status

Authorization Section

 

Body Section

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!!!