Monthly Archives:December 2017

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

ByHariharan Rajendran

DAX – Top N for each Previous & Current Year

This article covers a real-time scenario which was asked by one of the customers.

Requirement:

Show the sales of the top 5 countries for the current year and previous year.

Challenges:

The above requirement seems to be simple, but we don’t have the same customers on the previous and current year.

  1. If we use a simple bar/column chart, then we will get multiple standalone bars or columns.
  2. We can use a stacked column chart but if we apply the top 5 then it will apply on the whole dataset, but we need to apply for each year.

Solution:

Follow the below steps

  1. Create a DAX table with below logic
    1. TOP 5
    2. Filter – Current Year & Previous Year
    3. Union the above results

Detailed Steps:

I am using below dataset (Sales).

Country Year Value
A 2018 12
B 2018 34
C 2018 56
D 2018 17
E 2019 17
F 2019 19
G 2019 11
H 2019 23
I 2017 16
J 2017 18
K 2017 23
L 2017 26
M 2017 56

 

Used the DAX syntax to create another table

_Top5 = UNION(

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY()),”0000″)),

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY())-1,”0000″))

)

Now, create a stacked column chart which will give the expected result.

post your comment, if you have any other approaches.

ByHariharan Rajendran

Power BI Inventory using Power BI REST API

This blog post explains how the REST API can be used to prepare the Power BI Inventory Report. On this inventory report, we can show below items which almost covered all the contents from Power BI.

  1. List of Workspaces
  2. List of Datasets
  3. List of Reports
  4. List of Dashboards
  5. List of Tiles
  6. List of Data Gateways
  7. List of Data sources on the gateway
  8. List of users and their access.

 

Those REST APIs are wrapped into PowerShell script and exporting the result as csv files. You can schedule this PowerShell to get the latest contents from Power BI on a daily basis.

Pre-Requisite:

  1. Register an app and get the Client ID and Use it on the below script

 

[sql]

# Parameters – fill these in before running the script!

# ======================================================

 

# AAD Client ID

# To get this, go to the following page and follow the steps to provision an app

# https://dev.powerbi.com/apps

# Level of access: check all boxes

 

$clientId = “xxxxxx-xxxx-xxx-xxx-xxx”

 # End Parameters =======================================

 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

}

 function get_groups_path($group_id) {

    if ($group_id -eq “me”) {

        return “myorg”

    } else {

        return “myorg/groups/$group_ID”

    }

}

 # PART 1: Authentication

# ==================================================================

$token = GetAuthToken

 # Building Rest API header with authorization token

$auth_header = @{

   ‘Content-Type’=’application/json’

   ‘Authorization’=$token.CreateAuthorizationHeader()

}

 # ==================================================================

# Get the list of groups

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

$all_groups = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = (“C:\Users\Hari\Documents\Allgroups.csv”)

$all_groups | Export-csv $filePath

 # ==================================================================

# Get the list of gateways

$uri = “https://api.powerbi.com/v1.0/myorg/gateways/”

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = (“C:\Users\Hari\Documents\AllGateways.csv”)

$all_gateways | Export-csv $filePath

 # ==================================================================

# Get the list of gateways data sources

$uri = “https://api.powerbi.com/v1.0/myorg/gateways/”

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

$all_gateways_id=$all_gateways.id

 foreach ($gateway in $all_gateways_id)

{

     $uri = “https://api.powerbi.com/v1.0/myorg/gateways/$gateway/datasources”

   $all_gateway_DS = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

  }

$filePath = (“C:\Users\Hari\Documents\AllGatewaysDS.csv”)

$all_gateway_DS | Export-csv $filePath

 # ==================================================================

# Get the list of datasets from Groups

$all_datasets=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets”

   $datasets = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_datasets+=$datasets

  }

$filePath = (“C:\Users\Hari\Documents\alldatasets.csv”)

$all_datasets| Export-csv $filePath

 # ==================================================================

# Get the list of reports from Groups

$all_reports=@()

foreach ($group in $all_groups)

   $group_id = $group.id

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

   $reports = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_reports+=$reports

  }

$filePath = (“C:\Users\Hari\Documents\allreports.csv”)

$all_reports| Export-csv $filePath

 # ==================================================================

# Get the list of dashboards from Groups

 $output_dashboard = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards”

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   Group_Id = $group.id

   GroupName = $group.name

   displayName = ($all_dashboards.displayName -join ‘,’)

     } | Select-Object Group_Id,GroupName, DisplayName

     }

$filePath = (“C:\Users\Hari\Documents\alldashboards.csv”)

$output_dashboard| Export-csv $filePath

 # ==================================================================

# Get the list of tiles

$all_tiles=@()

foreach ($group in $all_groups)

   $group_id = $group.id

    $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards”

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   foreach ($dashboard in $all_dashboards)

   {

     $dashboard_id = $dashboard.id

   $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards/$dashboard_id/tiles”

   $tiles = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   }

   $all_tiles+=$tiles

  }

$filePath = (“C:\Users\Hari\Documents\alltiles.csv”)

$all_tiles| Export-csv $filePath

 # ==================================================================

# Get the list of users from Groups

 $output = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/users”

   $all_group_user = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   GroupName = $group.name

   Access = ($all_group_user.groupUserAccessRight -join ‘,’)

   DisplayName = ($all_group_user.displayName -join ‘,’)

   PricipalType = ($all_group_user.principalType -join ‘,’)

   } | Select-Object GroupName,Access, DisplayName, PricipalType

     }

    

$filePath = (“C:\Users\Hari\Documents\AllUsers.csv”)

$output | Export-csv $filePath

 ## ==================================================================

## Get the list of datasets from Groups with Group Names

 

$alldatasets_GP = foreach ($group in $all_groups)

   $group_id = $group.id

  $group_name = $group.name

   $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets”

   $alldatasetsGP = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

       New-Object -Typename psobject -Property @{

   GroupId = $group.id

   GroupName = $group.name

   Dataset_Id = ($alldatasetsGP.id -join ‘,’)

   DisplayName = ($alldatasetsGP.name -join ‘,’)

     } | Select-Object GroupName,Dataset_Id, DisplayName

 }

$filePath = (“C:\Users\Hari\Documents\alldatasets_Groups.csv”)

$alldatasets_GP | Export-csv $filePath

[/]