Power BI and Conditional Columns

aaeaaqaaaaaaaamlaaaajdy5y2yxzdyzlwy3ngmtndblmy05zthklta2mjnkntm0mjewyq

During business reporting, you might have come across various scenarios, where data is needed for some columns and for some need to be grouped as ‘Other’.

Do you know how we can do this easily and effectively in Power BI?

Read my article Power BI and If Then Else Conditional Columns on LinkedIn

Categories: Uncategorized

Power BI and master-detail JSON

December 7, 2016 Leave a comment

Power BI

Now a days, I am spending sizable amount of time with Power BI. Power BI is suite of business analytics tools used for discover, analyze data and share the insights. Power BI empower users and helps to deliver interactive dashboards and reports.

0

Power BI can combine your on premise organizational data as well as data from cloud based business apps as well. It can also combine data from various source like, databases, files,  web services and can visualize the data for easy analysis.

JSON as a Data Source

I have been using NoSQLs a lot for my recent developments. These NoSQLs are storing a data in JSON format. Even when we are having integrations with frond end programs, then also we are exposing JSON data with the help of REST APIs. Knowing use of JSON at various layers within the application, I was more interested how we can consume a JSON in Power BI and how we can visualize the same.

Typically simple JSON structure can be easily transformed and data can be cleaned. But when it comes to a JSON structure with master-detail pattern, then users or data analysts may face a challenge to structure the data the way Power BI can use for analysis as well for visualization.  This blog will walk you through the steps which can be carried out for master-detail pattern in Power BI from a JSON based data structure.

Master-detail JSON

I am considering a very famous Northwind database for demonstration. Let’s consider we are storing Order and OrderDetails  in a JSON format like below. This is a typical master-detail pattern.

0-1

Power BI Desktop

Power BI Desktop is a free report authoring tool. With the help of this tool, we will be getting the data from JSON and the transforming the same data for reporting purpose.

I have already saved above JSON data in orders.json file.

Now let’s follow below steps in Power BI Desktop,

1. Select Get Data option and choose JSON and click Connect.1

2. Select orders.json file and click Open.2

3. After opening a file, Power BI Query Editor will open directly.

4. Click on List row from the table and see you are able to see List of records as highlighted below,

4
5. Now Click on option Into Table to convert this list into a record.

6. You will see a table with JSON attributes as Results and ResponseStatus as below,6

7. Click on Results to further transform this data7

8. Click on To Table option from tab menu to launch below screen,

8

9. Click OK to see below screen where Column1 is shown with split icon

9

10. Click on split icon shown with two arrows at right corner to split the data using below screen

10

11. Select columns to be expanded and click OK and see Order and OrderDetails list has been expanded now

11

12. Now click on Column1.Order split icon and again select columns to be expanded.12

13. Once you click OK, you should see data as below,13

14. So far we have now expanded Column1.Order column. You can see above how column names are appearing for fields from Order. Look at now Column1.OrderDetails this is still a List type.

14

15. Now here is an important step, let’s add now an Index Column using Add Index Column option from Add Column tab before we transform OrderDetails.15

16. Once you add an index column, rows from Orders will be indexed and shown with Index column as below. This shows we have total 6 Orders in JSON16

17. Now  select to Column1.OrderDetails which is of type List and click on split icon to expanded records from OrderDetails as below. Here when we clicked on split icon, it duplicated Orders data along with Index column as well. Still we see Record under Column1.OrderDetails column.17

18. Click on split icon to select columns to be expanded as below.18

19. Click OK to expand data for selected column. Now Order and OrderDetails both data can be seen under one table as below,19
20. Let’s verify our data for each column, e.g. Column1.OrderDetails.Quantity, Column1.OrderDetails.UnitPrice are of Text Data Types. Change their data types to Whole Number and Decimal Number respectively. This is done using tab Transform > Data Type20

21. Now this is a like a typical flat data structure where Order data is duplicated based on records in OrderDetails for that order21

22. The best thing which I can see in Power BI is, it has tracked all the steps of transformation and can be seen at APPLIED STEPS. You can very well go back to each steps and see how transformation has been done just by clicking on these steps.22
23. Now to save all the transformations done in a query form, click Close & Apply23

24. Select Data option table like icon as highlighted from Left Navigation of Power BI, to see flat structured data as below.24Now this data can be used for reporting purpose with all the transformations done.

25. Just to demonstrate, I have added couple of visuals in Power BI to see how master detail records are seen. I added a slicer visual so that I can select whatever Order Number and then beside Slicer, added a Clustered Column Chart.
Now select OrderId from Slicer and see in chart data is showing ProductId, Quantity and UnitPrice for selected OrderId. Refer JSON, Selected Order and Order Details in one view side by side to verify the transformation and mapping of data.

25

Awesome! Keep Transforming…

 

Easily Create An Analog Clock Using moment.js And JQuery

July 7, 2016 Leave a comment

Refer to the following screenshot. This is what we will be building in this post,

clock
Figure 1: JQuery and CSS3 based Analog Clock

Follow the below steps to achieve what has been shown in Fig.1.

  • Create a new html page as clock.htmlandcopy paste below code,
  1. <!DOCTYPE html>  
  2. <html lang=“en”>  
  3.   
  4.     <head>  
  5.         <title>Analog Clock using moment.js and jQuery</title>  
  6.         <meta charset=“utf-8”>  
  7.             <meta name=“viewport” content=“width=device-width, initial-scale=1”>  
  8.                 “text/javascript” src=https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js&#8221;>  
  9.                       
  10.                     “text/javascript” src=https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.11.1/moment.min.js&#8221;>  
  11.                           
  12.                         <link rel=“stylesheet” href=“clock.css”>  
  13.                             “text/javascript” src=“clock.js”>  
  14.                                   
  15.     </head>  
  16.   
  17.     <body>  

  18. “clockarea”>

  19. “clock”>

  20. “hour” class=“hourhand”>div>

  21. “minute” class=“minutehand”>div>

  22. “second” class=“secondhand”>div>
  23.                             

 

  1.                             </div>  
  2.                             <br/>  
  3.                             <div class=“currentDateTime”>div>  
  4.     </body>  
  5.   
  6.     </html>  

The above code shows reference to moment.js and jQuery. This also has a reference to custom files like clock.css which we will be generating in next step. And all the clock functionality will be added in step 3 as a part of clock.js.

As a part of the html body, this is just a DIV based implementation and not  any canvas type of object. DIVs are self-explanatory here.

  • Create a new stylesheet file as clock.css and copy and paste the following styles to it,
  1. body  
  2. {  
  3.     font – family: Verdana;  
  4. }  
  5. .clockarea   
  6. {  
  7.     height: 200 px;  
  8.     width: 200 px;  
  9.     position: relative;  
  10.     border – radius: 5 px;  
  11.     background: black;  
  12. }  
  13.   
  14. .clock  
  15. {  
  16.     height: 100 % ;  
  17.     width: 100 % ;  
  18. }  
  19.   
  20. .clock: after   
  21. {  
  22.     position: absolute;  
  23.     top: 50 % ;  
  24.     left: 50 % ;  
  25.     width: 12 px;  
  26.     height: 12 px;  
  27.     margin: -6 px00 – 6 px;  
  28.     background: blue;  
  29.     border – radius: 6 px;  
  30.     content: “”;  
  31.     display: block;  
  32. }  
  33.   
  34. .hourhand   
  35. {  
  36.     width: 0;  
  37.     height: 0;  
  38.     position: absolute;  
  39.     top: 50 % ;  
  40.     left: 50 % ;  
  41.     margin: -4 px0 – 4 px – 25 % ;  
  42.     padding: 5 px05px25 % ;  
  43.     background: blue; – webkit – transform – origin: 100 % 50 % ; – ms – transform – origin: 100 % 50 % ;  
  44.     transform – origin: 100 % 50 % ;  
  45.     border – radius: 4 px004px;  
  46. }  
  47.   
  48. .minutehand  
  49. {  
  50.     width: 0;  
  51.     height: 0;  
  52.     position: absolute;  
  53.     top: 50 % ;  
  54.     left: 50 % ;  
  55.     margin: -40 % -3 px0;  
  56.     padding: 40 % 3 px0;  
  57.     background: blue; – webkit – transform – origin: 50 % 100 % ; – ms – transform – origin: 50 % 100 % ;  
  58.     transform – origin: 50 % 100 % ;  
  59.     border – radius: 3 px3px00;  
  60. }  
  61.   
  62. .secondhand  
  63. {  
  64.     width: 0;  
  65.     height: 0;  
  66.     position: absolute;  
  67.     top: 50 % ;  
  68.     left: 50 % ;  
  69.     margin: -40 % -1 px00;  
  70.     padding: 40 % 1 px0;  
  71.     background: blue; – webkit – transform – origin: 50 % 100 % ; – ms – transform – origin: 50 % 100 % ;  
  72.     transform – origin: 50 % 100 % ;  
  73. }  

 

  • Create a new JavaScript file clock.js and copy paste the following script code to it,
    1. $(function()  
    2.   {  
    3.     setInterval(function()  
    4.         {  
    5.         var now = moment(),  
    6.             second = now.seconds() * 6,  
    7.             minute = now.minutes() * 6 + second / 60,  
    8.             hour = ((now.hours() % 12) / 12) * 360 + 90 + minute / 12;  
    9.   
    10.         $(‘.hourhand’).css(“transform”“rotate(“ + hour + “deg)”);  
    11.         $(‘.minutehand’).css(“transform”“rotate(“ + minute + “deg)”);  
    12.         $(‘.secondhand’).css(“transform”“rotate(“ + second + “deg)”);  
    13.   
    14.         //Display date and time just below our clock   
    15.         $(‘.currentDateTime’).text(moment().format(‘MMM DD YYYY HH:mm:ss’));  
    16.   
    17.     }, 1000);  
    18. });  

    As a part of JavaScript, we are just getting the current timestamp using moment.js. Once we get the current timestamp, we are calculating hour, minute, and second hand angles based on trigonometric basics.

    Once we get these angles, we are using CSS3 features like “transform” and “rotate” to move these rendered hands. Since we are calculating these angles every second and rotating these DIVs, we will be seeing an actual analog clock effect.

    At last, we are showing current time which is graphically being generated using the above logic.

 

Categories: Javascript Tags: , , ,

SharePoint Health Score

July 7, 2016 Leave a comment

In my experience of SharePoint 2010 Custom Development, as well as SharePoint 2013 development, I have always been curious to see how my on premise SharePoint Servers’ Web applications are performing from a performance perspective.

The very first step from a monitoring point is to refer to the SharePoint Health Score.

Health Score

SharePoint Health Score determines the health of SharePoint Servers from zero to 10, zero beingthe  most healthy state of servers. SharePoint calculates this health score for each and every request. While calculating this score, some of the key parameters considered are concurrent requests, memory usage etc.

But as a developer you will have questions such as: Is this made available to the developer? Do I need admin access or server access?

The answer to all your above questions is the HTTP Response Header X.

SharePointHealthScore. SharePoint calculates this score for each and every request and seds the same custom header X-SharePointHealthScore.

How to access SharePoint Health Score easily

I am using Chrome browser. Followthe  below steps to access SharePoint Health Score,

  1. Open Chrome Browser
  2. Go to ‘More tools’, then ‘Developer tools’

    Developer
                                                                Figure 1: Chrome Developer Tools

  3. Open Network tab and click check box ‘Preserve log’,

    Chrome
              Figure 2: Chrome Developer Tools > Network tab: Preserve Log

  4. Click on left side black icon i.e. to start recording network activity. This icon will turn red and then hit your SharePoint URL.
  5. Network tab will show all the requests and responses as in the following:

    Network
                               Figure 3: Network tab showing requests logged.

  6. Select Home.aspx from all the requests to open details about this activity,

    Health Score
                Figure 4: Health Score custom header value shown as 0 meaning healthy state.

As shown above, my server/Web application is ina  healthy state and I should not be worried about my development done so far. But when the value is approaching 10, what needs to be done will be discussed separately.

This way, developers can take a look at SharePoint Health Score just by using their browser based Developer Tools.

Pl. Note: Although I am capturing SharePoint 2013 URL, all the above steps can be followed to monitor the health score for SharePoint 2010 URLs as well.

In case of Internet Explorer 11, the path Tools> F12 Developer Tools >Network tab>Response Headers can be followed to observe health score custom header.

Categories: SharePoint 2010

Funnel Charts Using JavaScript – D3Funnel.js

July 7, 2016 1 comment

Funnel Charts

Funneling or pipeline management has always been an integral part of sales cycles within industries. The following example will give you an idea of how it looks

Funnel Chart showing sales stages
Fig 1: Funnel Chart showing sales stages.

In this article, we will actually build a funnel chart using d3-funnel.js . This d3-funnel is an extensible open source library built on top of D3.js. So while developing d3funnel charts we need to refer first to D3.js and then d3-funnel.js

Use Case – Showing a typical funnel for our sales cycle in funnel chart

Consider we already have a system which is capturing data about all the proposals or opportunities. All these opportunities will be shown in a funnel shape along with their deal value. Key stakeholders can review this funnel and make their decisions about further sales.

Now let’s follow the below steps for our use case.

  1. Create index.html and copy paste below code,
    1. <!DOCTYPE html>  
    2. <html lang=“en”>  
    3.     <head>  
    4.         <title>Funnel Chart Sample</title>  
    5.         <meta charset=“utf-8”>  
    6.             <meta name=“viewport” content=“width=device-width, initial-scale=1”>  
    7.                 <link rel=“stylesheet” href=http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css&#8221;>  
    8.                     <script src=https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js&#8221;></script>  
    9.                     <script src=http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js&#8221;></script>  
    10.                 </head>  
    11.                 <body>  
    12.                     <div class=“container”>  
    13.                         <div class=“page-header”>  
    14.                             <h2>  
    15. Funnel Chart</h2>  
    16.                         </div>  
    17.                         <div class=“row”>  
    18.                             <div class=“col-lg-6”>  
    19.                                 <div class=“panel panel-default”>  
    20.                                     <div class=“panel-heading”>  
    21.                                         <span class=“glyphicon glyphicon-equalizer”></span>Sales Funnel   
    22.   
    23.                                     </div>  
    24.                                     <div class=“panel-body”>  
    25.                                         <div id=“funnel”></div>  
    26.                                     </div>  
    27.                                 </div>  
    28.                             </div>  
    29.                         </div>  
    30.                         <script src=https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.16/d3.min.js&#8221;></script>  
    31.                         <script src=“d3-funnel.min.js” type=“text/javascript”></script>  
    32.                         <script src=“app.js” type=“text/javascript”></script>  
    33.                     </body>  
    34.                 </html>  

    In the above code base, you will notice references for D3.min.js and d3-funnel.min.js

    As a standard, I am displaying my chart in a bootstrap panel.

    To generate funnel chart, following ‘id’ is important from our index.html,

     

  2. Behind this index.html, we have implementation in our app.js.Refer our app.js as below,
    1. $(document).ready(function()
    2. {
    3.     showfunnel();
    4. });
    5. function showfunnel()
    6. {
    7.     var data =
    8.     [
    9.         [‘PROSPECT’, 2500000],
    10.         [‘PROPOSAL’, 1700000],
    11.         [‘NEGOTIATION’, 1000000],
    12.         [‘DEAL’, 500000]
    13.     ];
    14.     var options =
    15.         {
    16.         chart:
    17.           {
    18.             curve:
    19.             {
    20.                 enabled: true
    21.             }
    22.         }
    23.     };
    24.     var chart = new D3Funnel(‘#funnel’);
    25.     chart.draw(data, options);
    26. }

    In the above code base, you will notice function showfunnel() actually is rendering chart using array data[].

    Options – provides various options to render charts. Currently I am using funnel to be displayed in curve. So used {chart: {curve:{enabled:true}}}.

  3. Open index.html in browser and see how funnel is displayedCurved Funnel Chart showing sales stages
    Fig 2: Curved Funnel Chart showing sales stages.

    Chart Options

    Now we will try changing some chart options,

    Go back to app.js and at options, use the below line to render chart with bottom section pinched a little bit. And refresh index.html.

    1. var options = {chart: {bottomPinch:1}};

    Pinched Funnel Chart
    Fig 3: Pinched Funnel Chart.

    If you want to display funnel section height based on data then use the below options. After rendering chart, observe height for each section and their values are in proportion.

    1. var options = {block: {dynamicHeight: true}};

    Funnel Chart with dynamic height based on values
    Fig 4: Funnel Chart with dynamic height based on values.

So stakeholders can now visualize opportunities or proposal data and with such a funnel decide where to focus. And also they can judge their pipelines.

Office Delve – Boards

January 17, 2015 1 comment

Microsoft very recently introduced a new feature to Office Delve called Boards.

Delve boards offer a very unique user experience when it comes to displaying information; knowing users’ context and how users’ are connected within an enterprise or in their networking.

Delve boards help us to group related information together and share with other users in a very simplistic manner. Every Delve card you see, shows a link at the bottom of your card. You can either create a new board or use existing boards to tag the Delve card.

Office Delve Boards

Let me explain with an example, how boards help user to tag a content and organize the same as below,

Suppose as a SharePoint 2013 branding team member, I would like to organize all my documents related to SharePoint Branding including Case Studies, Branding artifacts along with best practices and guidelines. This way when I am looking at SharePoint 2013 Branding board, all the documents tagged as ‘SharePoint 2013 Branding’ will be shown regardless of their source. Remember, information is not tagged at the source but when it is shown under Delve.

Let me walk you through above example with Delve and Delve board. Assuming, I have already uploaded all my branding related information in a document library or similar information has been shared with me. Delve Cards are showing me all this information in a card based interface.

Launching a Delve

You can launch Delve from Office 365 App Launcher icon as shown below,

Launching Office Delve

Fig. 1 Launching Office Delve.

Office Delve

Fig. 2 Office Delve View.

Creating a Delve board and tagging to the board

Every Delve card provides a link to create a new board or tag the current card with already created boards. Refer below series of screens to get an idea of how a board can be created and Delve card can be tagged.

Create a board

Fig 3. Series of screens showing a new delve board ‘SharePoint 2013 Branding’ creation from the card itself.

The way in which we created a single board, we can also create multiple boards. These boards can be based on projects, technology or based on interest areas. And these Delve cards can be tagged for multiple boards and can be displayed under them respectively. To add a Delve card under multiple boards, use ‘+’ link shown beside existing board links.

Removing Delve information from a board

 To remove tagged information from a specific board, just select the card from delve or from a board and right click on board name and select link ‘Remove from boards’. This will display ‘X’ icon, click on it to remove the information from the board.

 Remove board

 Fig 4. Removing a tagged information from a board.

Searching Delve Boards 

When it comes to accessing boards within enterprise, boards are open to everyone within an enterprise. Users can see these boards and also contribute to them

Now if I would like to search all branding related information, I will use Search option as shown below. After searching, left navigation will show, Delve Boards meeting the criteria. You can now see ‘SharePoint 2013 Branding’ board link in left navigation.

Search Results for Branding

 

Fig 5. Boards list shown in left navigation and Search Results for matching criteria.

Select ‘SharePoint 2013 Branding’ board from left navigation to display all the relevant information tagged for this board.

Board Information

 

Fig 6. Information shown under selected board.

Sharing and Following a Delve Board

Every board can be searched and followed/unfollowed using ‘Follow’ or ‘Unfollow’ links. You can also share a link of entire board using ‘Send a link’ from the board itself.

Share a board

Fig 7. SharePoint 2013 Branding board sharing and following.

Happy Delve boarding…

This way we can create different boards based on technology, projects, and of interest areas or for ideas and concepts and share with our teams within enterprises for effective collaboration.

Although I am finding some similarities at concept level between Curah! and Delve Board, I am hoping a wonderful journey as we go on collaborating and discovering information across the enterprises who are on Office 365. So keep boarding the information and get the feel of Delve boards.

SharePoint 2010 Alerts and Subscriptions

November 4, 2012 Leave a comment

Overview

Many of the times, information presented on web sites is categorized based on either some common interest or common element. In the world of IT, we have several types of information which can be either grouped or categorized as Security, Web Development, Legacy, Cloud and Virtual Computing and Mobile computing. People around the globe when interacting with such information would like to prefer accessibility to such information based on predefined categories or classification.

Information in SharePoint 2010 and Alerts

In SharePoint 2010 world, any information or content is stored; it can be tagged and or categorized. Information can be either a blog or a wiki or a document. This information can be presented to end user based on category. But many times, users (for this article, I am considering users as IT professionals who are aware of basics of SharePoint 2010) would prefer an email based update or an information instead of directly visiting, where the information has been stored and presented. SharePoint 2010 Alerts enable end users to subscribe themselves based on their interest and can get the updates in their email inbox directly using OOTB functionality just by mere configurations.

Knowing typical alerts and subscribing requirements, for this article, we will consider a typical wiki site which will have wiki pages tagged based on predefined wiki categories.

Creating a Wiki Site

Create a wiki sub site within a publishing portal as shown below,

Creating Wiki Categories

Wiki pages created under this sub site needs to be tagged based on Wiki Categories. Typically these wiki categories considered as a managed metadata. This is SharePoint 2010’s default behavior for Wikis. To define our information categories, let’s first ensure, Wiki Categories are created as a part of Managed Metadata using Managed Metadata Service from Central Administration. Wiki Categories can be seen as below,

Mapping Wiki Categories to Wiki Page Library

Wiki Categories which are created from Central Administration as above can be mapped to Wiki Pages Library using Library Settings. This can be done as below,


Now whatever wiki pages will be created, can be tagged using centrally managed metadata based terms.

View Creation Based On Wiki Categories

As an IT professional, I am interested in information updates in Security area. All the Wiki Pages which are falling under Security category should be logically grouped and any changes or additions happening to them should be notified to me through an email. To enable this we need to first create a view on Wiki Pages library. Pl. find below how to create a view based on Wiki Category as ‘Security’.

Similar to ‘SecurityView’, other views can also be created for users to subscribe updates in that category. (View has been named as a ‘SecurityView’ just to follow some standard showing ‘Filter ’as ‘Wiki Categories’ is equal to ‘Security’)

Create a New Alert

Since I am interested to know any new addition or change from Wiki pages from Security category, I will create an alert on this Security View using Manage Alerts functionality from SharePoint 2010 as below,

Here is an important note, choose options for ‘Send Alerts for These Changes’, as we are interested in changes from SecurityView. Select an option of ‘Someone changes an item that appears in the following view’ and SecurityView from the drop down.

With an alert saved on a SecurityView for me, we are all set to receive email based updates, when any new wiki gets added or changed falling under ‘Security’ category.

Let’s create a wiki page named ‘SharePoint 2010Security’, as if this page is getting added newly to site. And once this is added, should send me an alert about this newly added and published page for ‘Security’ category.


Let’s add content for SharePoint 2010 Security wiki and tag it as ‘Security’ at ‘Wiki Category.

After finishing wiki page content creation, once we save newly created page, an email will be sent and you should see a newly added wiki page of SharePoint 2010 Security in an email as below. (Assuming SharePoint 2010 Timer Job and SMTP based configuration is already done and working as expected.)


Having configured predefined views based on predefined categories, IT Pros can configure or subscribe their own alerts and receive an email based alerts.

Categories: SharePoint 2010