Monthly Archives:November 2016

<
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

List.Contains M Query | Importance of M Query

Hi, Happy new year to everyone.

In this blog post, I am explaining the importance of learning M Query. Let me tell you the scenario.

Scenario:

I have a simple table as like below,

I need to create a new column based on the Category column. The logic is If Category is A then Yes otherwise No. This can be easily achieved by adding the if-else condition as below.

If [Category]=”A” then “Yes” else “No”.

In the same scenario, if I need to include A & B both then I need to modify the expression as below.

If [Category]=”A”  or [Category]=”B” then “Yes” else “No”.

Let us take the below table and I want to consider A,B,C,D,E,F. if any of these values then “Yes” otherwise No. In this case, if I use if and else then I  need to keep add or condition to cover up all the values. The final expression would be as below.

 

If [Category]=”A”  or [Category]=”B” or [Category]=”C”  or [Category]=”D” or [Category]=”E” or [Category]=”F”   then “Yes” else “No”.

Even sometimes, we may get more conditions in the above expression. The above expression keeps grow based on the number of values we want to analyze.

 

Solution – Simple and Easy

As I explained, this is logic almost every Power BI developer prefer or use on their Projects. If you know M Query or Power Query then you can simplify the logic. Let me explain, how to achieve the same result with simplified logic.

I am introducing one of the M Query function called “List.Contains”. This is one of the Powerful functions. Use this function to minimize the expression.

Syntax for List.Contains

List.Contains(list as list, value as any, optional equationCriteria as any) as logical

 

if(List.Contains({“A”,”B”,”C”,”D”,”E”,”F”},[Category])=true) then “Yes” else “No”

That’s it. It gives you the same result. If you don’t want to use the hardcoded value you can create a list with A to F values again you can get the main table and use that list on the expression as below.

The above T3 list used on the below expression.

Explained the same in video. Please check it.

Use this M function to simplify the expression. Please leave your comments below. Thanks

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

ByHariharan Rajendran

DAX – Adjust the Total value – Conditional Total in DAX

In this article, I want to show you how I achieved a business solution using DAX. Let me explain the requirement.

I have a below table and customer wants the data in the below format.

Country Color Quarter Product
India Green 2019-Q1 P1
USA Blue 2019-Q1 P2
UK Red 2019-Q1 P3
India Red 2019-Q2 P1
USA Blue 2019-Q2 P2
UK Amber 2019-Q2 P4

 

Expected Solution

Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue)
India 100% 0%
USA 100% 100%
UK 0% 0%
Total 67% 33%

 

Actually, the customer wants to show the count as 1 or 0 if green or blue exists on each country so the required result would be like below.

Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue)
India 1 0
USA 1 1
UK 0 0

 

First step: we can target blue and green count.

Green_Blue = CALCULATE(

COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

The next upgraded version is handling blank with zero.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal)

Next level, we can divide the value to get the percentage.

GB =

Var GBVal  = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal  = BLANK(),

0,

GBVal  / CALCULATE(

COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])))

Then finally, we need to tweak the result on the divide section to get the expected result. This will adjust the total section.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal / if(

DISTINCTCOUNT(‘Product'[Country])>1,

CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])),GBVal))

We got the expected result. Please provide your feedback and comments.

 

ByHariharan Rajendran

Power BI Shared Datasets – Cheat Sheet

In this article, I want to show you the cheat sheet of the Shared Dataset inside Power BI. I tried to cover all the information related to shared dataset.

As you know, the Shared dataset is one the latest update from Power BI team which opens a new channel to data analyst & business users to create reports from the available datasets. It reduces their work on building the data model from scratch.

It also helps the organization to maintain a single version of the truth on data.

Please share your feedback and comments below.