Author Archives: Hariharan Rajendran

ByHariharan Rajendran

Power BI Embedded Service Principal – Postman

Microsoft Power BI team released a new update recently on Power BI Embedded to use service principal whenever it required. In this article, I am explaining that how to add service principal into Power BI service workspace which is not a direct way.

Service principal should be added into Power BI using REST API. Follow the below steps.

First create Power BI new workspace.

Add the service principal as a user with admin access. To perform this step, we need to use Power BI REST API.

I have used postman to complete this step.

First, I generated the authorization token. POST https://login.microsoftonline.com/common/oauth2/token?

Then added the service principal into the Power BI workspace.

 

ByHariharan Rajendran

Dataset Refresh in PowerApps Custom visual

As we know, Power BI can be integrated with Power BI. Check the below approaches.

  1. In Power Apps, we can use Power BI tiles
  2. In Power BI, we can use Power Apps custom visual.

 

This article explains the latest update to the Power Apps custom visual. Let us take a scenario.

I need to embed the Power Apps with in Power BI report. Whenever an item is added through PowerApps, I need to refresh the dataset used for the Power BI report.

For the above scenario, we need to use Microsoft Flow to achieve the result. In Microsoft flow, we need to have custom connector to refresh the Power BI dataset.

From the latest update from PowerApps custom visual, we no need to go to the above step. I mean, we no need to use Microsoft flow to refresh the dataset. We have refresh method added with Power BI integration data source itself.

It is a very good feature for Power BI + PowerApps scenarios.

ByHariharan Rajendran

DAX Group by and Filter

This post explains you that how DAX can be used for Group by and Filter logic. To explain this scenario, I am taking an example data.

Example Data:

I have a following table.

EmpID EmpDesignation EmpDepartment DesigFrom
1 A IT – Finance 01/02/2018
1 SA IT – Sales 05/08/2018
2 M Marketing 05/07/2018
2 SM HR 25/08/2018
3 A Sales 06/06/2018

 

Requirement

As per the requirement, I need to perform below actions.

  1. Date Slicer should be available to filter From and To date
  2. Based on the filter, a table of figure should display the latest designation.

Expected Result

Filter – Start Date: 01/02/2018 to 31/08/2018

EmpID EmpDesignation MostEffective
1 SA 05/08/2018
2 SM 25/08/2018
3 A 06/06/2018

 

To achieve the above result, we need to create a DAX script. Use the below DAX script

MosftEffectiveDate = CALCULATE(

MAX(Employee[DesigFrom]),

Filter(Employee, Employee[EmpDesignation] = EARLIER( Employee[EmpDesignation])))

 

Sometimes, you may receive error due to Earlier function. Check the another version of the script without Earlier.

MosftEffectiveDate =

VAR Desig = MAX(Employee[EmpDesignation])

RETURN

CALCULATE(

MAX(Employee[DesigFrom]),

Filter(Employee, Employee[EmpDesignation]=Desig))

 

ByHariharan Rajendran

Python Script to Event Hub – Power BI Sentiment Analysis ML Function

This article explains the python script to send the data to event hub.

As we know, event hub is azure service which is designed to capture the streaming events from different devices or applications.

The below python script sends the sentiment string to event hub.

import sys

import logging

import datetime

import time

import os

import itertools

from azure.eventhub import EventHubClient, Sender, EventData

 

logger = logging.getLogger(“azure”)

 

ADDRESS = “amqps://<eventhubworkspacename>.servicebus.windows.net/<eventhubname>”

 

# SAS policy and key are not required if they are encoded in the URL

USER = “RootManageSharedAccessKey”

KEY = “<Key>”

 

try:

    if not ADDRESS:

        raise ValueError(“No EventHubs URL supplied.”)

 

    # Create Event Hubs client

    client = EventHubClient(ADDRESS, debug=False, username=USER, password=KEY)

    sender = client.add_sender(partition=”0″)

    client.run()

   

    try:

        start_time = time.time()

        for i in range(1):

            print(“Sending message: {}”.format(i))

            a=”[{“+”ts”+”:”+str(1550321522)+”,”+”Message”+”:”+”\”good\””+”,”+”TestVal”+”:”+str(7)+”}]”

           

       

            print(a)

            sender.send(EventData((a)))

                     

           

    except:

        raise

    finally:

        end_time = time.time()

        client.stop()

        run_time = end_time – start_time

        logger.info(“Runtime: {} seconds”.format(run_time))

 

except KeyboardInterrupt:

    pass

 

 

Applications

The above script sends the data to event hub which can use used as an input to the AZ ML function in Stream Analytics.

The output of the stream analytics query can be used for Power BI streaming dataset.

 

ByHariharan Rajendran

Bing Image Search API – Python Script

This article explains how to use a python script to interact with Azure cognitive services – Bing Image Search.

Requirements

  1. Python 3 & libraries
  2. Bing Search API key – Azure Subscription

Bing Search API

Follow the below steps to create Bing Search API.

Step 1: Visit portal.azure.com

Step 2: Create a new resource – search for “Bing Search v7”

Step 3: Once created the API, you need to get API key. The base URL is same for everyone which is https://api.cognitive.microsoft.com/bing/v7.0/images/search

 

Python script

Use the below python script and replace the key.

import http.client, urllib.request, urllib.parse, urllib.error, base64

import requests, json

desc_url = ‘https://api.cognitive.microsoft.com/bing/v7.0/images/search’

headers = {

    # Request headers

    ‘Ocp-Apim-Subscription-Key’: ‘795bff7c586c43749d79a746dcebe12b’,

}

params = {

    # Request parameters

    ‘q’: ‘Hariharan Rajendran’,

    ‘count’: ‘5’,

    ‘offset’: ‘0’,

    ‘mkt’: ‘en-us’,

    ‘safeSearch’: ‘Moderate’,

}

desc_response = requests.get(

              desc_url, headers=headers, params=params)

desc=json.loads(desc_response.content)

print(desc)

You can change the parameter values to get the different result.

 

ByHariharan Rajendran

Raspberry Pi 3

This post helps you to identify the pins in raspberry pi.

In an earlier version of Raspberry Pi, the pin details are clearly specified on the board itself but it is not there on the latest version of Raspberry Pi.

To get the pin details you can type the below command on the console.

pinout

Running the above code will give the details as like below image.

I will be posting IoT related scripts and how Raspberry Pi can be integrated with Azure and Power BI.

ByHariharan Rajendran

PowerBI – Python Support

Microsoft introduced Python support from Power BI on August 2018 update.

PowerBI is supporting R visuals and PowerBI producing custom R visuals too. PowerBI team extended the data science capability of PowerBI by supporting python.

In this post, I am explaining the simple python plot in Power BI.

Download the latest version of Power BI. As python support in preview support, we need to enable the python support preview feature.

 

You should install the Python on your machine so that you can point the python home directory which is mandatory to run any python scripts inside PowerBI.

Once you have configured the setup then you need to test with simple code. Use the below code.

import matplotlib

import numpy as np

import matplotlib.pyplot as plt

x = np.linspace(0, 3*4, 500)

plt.plot(x, np.sin(x**2))

plt.title(‘Test Plot’)

plt.show()

 

The above code works perfectly and produced the plot on jupyter notebook and need to get the same result on PowerBI also.

Drag and drop the python visual into the canvas area. In the below script editor, reference any dummy column and then copy and paste the above script.

Finally, execute the script and it will produce the plot as like below.

 

Now, you can play with your python scripts.

ByHariharan Rajendran

DAX – RelatedTable Scenario

There is a scenario to find the total count of vendor based on the current year and the same customer should exist on the main transaction table.

Let me explain in detail.

Consider, I have a table called vendors which have very simple columns like below.

Vendor ID Vendor Name Created Date
101 V1 Wednesday, February 22, 2017
102 V2 Thursday, December 28, 2017
103 V3 Friday, February 2, 2018
104 v4 Monday, May 7, 2018

 

And Transaction table like below.

Trans ID Vendor ID QTY Sale Amount OrderDate
1 101 5 1500 Tuesday, August 22, 2017
2 101 6 2588 Saturday, December 30, 2017
3 102 8 4500 Monday, April 2, 2018
4 103 12 8000 Thursday, June 7, 2018

 

On the above tables, I want to show the total count of vendors where they are available in the transaction table and their created date should be the current year.

The expected result is “1” as Vendor 103 only available in Transaction table and created date is the current year 2018.

Rest of the rows are not matched with this scenario.

There are various methods are available to achieve the solution. In this post, I am explaining the very simple method.

Step 1: Create a new calculated column on the table Vendors using below script.

IsMatch = IF(Format(TODAY(),”YYYY”) = FORMAT(‘Customer (2)'[Date],”YYYY”), “Match”, “NoMatch”)

It is adding a new column with values like match and nomatch.

 

Step 2: Create another calculated column on the vendor table using below script. We need to use the above-created column.

Count = CALCULATE(IF(ISBLANK(MINX(RELATEDTABLE(‘FACT’),”1″)),0,1),’Customer (2)'[Test]=”Match”)

That’s it. We can use the count column for the result.