Infographic using Power BI

With more demand and acceptance of data visualization, we have to deal with different kind of audiences. Some of those are not having analytical minds and simply grab information through graphics and cosmetics. There is also a need of awareness for masses for which statistical analysis and graphs are sometimes too much for general audience. In this scenario Inforgraphic is the best candidate to address these concerns.

I have experimented a report baased on open data of the government entity DEWA of Dubai. The result was amazing and really provide a simple information about operations and customers’ segmentation.

Report can be viewed through following link

Click here to view

Dynamic Infographics

Using Unicode Characters in Axis and Slicers

I recently did an experiment with Power BI report by using “Unicode characters” which was shared on LinkedIn. In this example I used selected characters in slicers and axis instead of text. This has created lot of interest among followers and received numerous requests to share details.

In reality it is a fairly simple approach where different Unicode characters were selected for different items and reactions used in this example.

Snapshot

Following three tables were created in Excel which were used in this report. For each dimension table I have added Unicode to represent icon.

Table Name: Serving

Item code Item Unichar
100 Coffee 127854
101 Ice Cream 127846
102 Soup 127861
103 Pizza 127829
104 Donuts 127849
105 Burger 127828

 

Table Name: Reaction

Rcode Reaction Unichar
1 Smiley 128578
2 Sad 128542
3 Neutral 128528
4 No Feedback 128566

 

Table Name: Responses (Only few rows shown here. Check Excel file for full list)

ItemCode Rcode
100 1
100 1
101 1
101 2
101 2
101 3
101 3
101 3
101 4
101 4
101 4
101 4
101 1
101 1

After importing excel sheet in Power BI I have added couple of calculated columns and measures. To create icon I have used following DAX.

For Item Icon to be used for Axis

ItemIcon = UNICHAR(ItemDefinition[Unichar])

 

For Item Icon and name to be used for slicer

IconAndItem = ItemDefinition[ItemIcon] & UNICHAR(10) &  ItemDefinition[Item]

 

Similar is used for calculated column for Reaction icon.

ReactionIcon = UNICHAR(ReactionDefinition[Unichar])

 

For Reaction Icon and name to be used for slicer

IconAndReaction =
ReactionDefinition[ReactionIcon] & UNICHAR(10) & ReactionDefinition[Reaction]

 

Following is the snapshot of relationship.

Model Snapshot

You may download sample excel file and PBIX file to evaluate more. This example is just to show how you can implement Unicode character. There can be numerous implementations and scenarios where icons can add value to your visuals and keep your report simple to grab the information easily.

To download only Excel file click the following link ReactionData

Complete files including PBIX can be download through  the following link

Unichar Example from EZPowerBI.com

Click here to see report in action View Power BI Report

For finding appropriate Unicode characters check the following links.

http://xahlee.info/comp/unicode_index.html 

https://www.vertex42.com/ExcelTips/unicode-symbols.html

Will be sharing more examples in future.

Subscribe to my blog for more surprises!

Cheers!

 

Append and Merge Tables in Power BI

There are scenarios where different departments are working separately and sending data to a central location for consolidation. Organizations where data governance is taken seriously, there are less efforts required in combining such data. However, in scenarios where there is considerable variance/difference between source files, it is difficult to append or merge multiple files.

This blog post is sharing basic features available with Power BI to tackle such scenarios. In this example we will use a sample excel file having necessary tables involved. Download Sample Excel File


Appending Tables

For Example related to append, following tables will be used.

List A

Code Item Price
1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11

List B

Code Item Price
1 Pencil 2
2 Eraser 3
5 Marker 12
6 Whiteboard 45

Step 1

Open a Power BI desktop file and click on “Get Data” icon from the ribbon. Select “Excel” and paste following URL in filename section.

https://ezpowerbi.files.wordpress.com/2018/07/ezpowerbi-com-merge-and-append-sample-data-012018.xlsx

Get Data

Note: If you have downloaded this file, you can select file from your folder.

Step 2

Select checkboxes of “List A” and “List B” from the list and click “Edit”

Step 3

You will notice that Power BI Query Editor window will open after previous step.

Note: If for any reason this window is not opening directly, then you have to click on “Edit Query” icon on the ribbon.

Step 4

Right click on Query Pane and select as follows
New Query>Combine>Append Queries as New

EZPowerBI.com Append Queries - 1

Step 5

In order to append files, you need to select following files as mentioned below. Since in our example, we are using only two tables, multiple tables can be selected if second option “Three or more tables” is clicked. Press “OK” when correct files are selected.

Append files

Step 6

You will notice that now both tables are combined and showing collective list.

1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11
1 Pencil 2
2 Eraser 3
5 Marker 12
6 Whiteboard 45

However this list has duplicate values. These were purposely entered in the list to show you how we can get rid of those.

Step 7

Click on “Remove Rows” icon from the ribbon and select “Remove Duplicates”. This action will give you final list with unique items from both lists.

Remove Duplicates

This will complete your append operation.

Final list will be like below.

1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11
5 Marker 12
6 Whiteboard 45

Step 8

You can rename new combined table as “Combined_Appended” for this example.

Step 9

Click on “Close and Apply” icon on the ribbon to complete this activity.


Merging Tables

Merging of tables is required when you are combining different tables with some common columns to make use of required columns of other tables. This is helpful in situations where product specification is maintained in one table while product prices are maintained in another. Following tables are used to this scenario.

1- Combined_Appended

2- List C

To import List C table, follow similar steps like mentioned in Step 1 of previous example (Append Tables).

Get Data

Step 2

Select checkbox of “List C” from the list and click “Edit”

Step 3

You will notice that Power BI Query Editor window will open after previous step.

Note: If for any reason this window is not opening directly, then you have to click on “Edit Query” icon on the ribbon.

Step 4

Right click on Query Pane and select as follows
New Query>Combine>Merge Queries as New

merge

Step 5

Now select files as demonstrated below. After selecting files click on “Code” column in both tables. This will make selected columns gray to identify columns used for merging tables.

Merge File Selection

Now click on “OK” to confirm merging.

Step 6

Once merged table is loaded, click on the icon on the top right corner as mentioned below to expand table columns.

expand

Step 7

You will see following window like below after completing previous step.

Select Columns

Now select Columns not available in first table to avoid duplicates. I our case we will select only “Box” and “Units” columns. In this example we can deselect “Use original column name as prefix” checkbox.

Select Columns2

Click on “OK” to complete this step.

Step 8

You will now see a merged table with combined columns like below.

merged file final

Step 9

Click on “Close and Apply” icon on the ribbon to complete this activity.


 

 

Power BI for smarter SMEs

powerbi

May be you are thinking that the word “Business Intelligence” is not mean for small or medium sized companies. May be your perception is that you need heavy investment to get started with your business intelligence. Sometimes your willingness to get going stops you because of initial licenses and expertise required. If you are really scared because of above factors then Power BI can make you laugh when you will complete your first BI implementation.

Why Power BI is different?

The answer is very simple. Power BI belongs to the Microsoft family which is very familiar to all kinds of users therefore working on a product with similar UI like MS Office family can reduce your learning path to few simple steps only. With cloud and pay as you go model, your upfront cost can be very nominal. There is no barrier to adopt Power BI as the desktop version is for FREE which gives you confidence of evaluating the product before making any serious investment.

Knowledge of Excel

All of us grown with Excel as a compulsory product in our office routine. None of the businessmen can claim that they have never used or heard about Microsoft Excel. This is a big encouragement for those who are planning to adopt Power BI as your MS Excel knowledge can help you up and running very quickly.

How to start?

See your desk and find the routine work you are doing and you will quickly notice that for some of the data you have you lack better insight. The simplest of all is the tasks assigned to your staff. If you are having a team of 5 members who are assigned tasks randomly, a good visualization can help you identify if your task distribution is doing justice? Sometimes you unintentionally overload a good employ without realizing that others are not fully utilized. In the same manner, you can make a visualization to see patterns of customer orders like which of the weekdays are busier so you can plan resources accordingly. For these kind of reports, information present on your desk can be easily transformed.

Take a giant leap

After you are comfortable with routine reports, it’s time to take a giant leap. You need serious efforts to go deep in to your data because in some cases you’re facing losses due to unknown reasons which can only be spotted with true implementation of business intelligence solution. Power BI can help you connect with your variety of data sources to merge, consolidate and transform data into a meaningful BI reports and visualization. Analytic features of Power BI will raise your eyebrows because you will be able to see trends and patterns never seen before.

Informed decision making for success

If you are still betting on your luck to succeed, then this may not help you in longer run. Companies are quickly adopting BI and Analytics to take informed decisions. May be your competitors are now relying on BI more than you and that may help your competitors beat you very often in critical business deals. Power BI can help you grab information quickly while raising flags to help you make the right move by saving more and investing in areas which are trending and ensuring better returns.

Explore Power BI

This is high time that a timely decision is taken to get started with BI and Analytics with Power BI. Once you get comfortable with reporting, drilldowns and analysis, you will definitely feel that you have stronger grip on your business than before.

EZPowerBI.com can be contacted for consultancy services for transforming your business to a data focused business.

Data Spadework for Power BI Visualization

powerbi

If you have decided to work on a report for your organization or customer, make sure that your spadework is up to the mark. This will help you in saving lot of hassle when making a report or visualization when making without proper spadework.
First of all you have to see what type of control you have on your data source.

1- Full Control
This must not be taken wrongly as this is related to access to data, here it means that how much you can influence the way and form you are getting data for your reports. If you are mandated to implement business intelligence in your company, then you have to set standards for your data. This may fall in data governance domain but to some extent you have to play a role to get data in meaningful format without unnecessary and inconsistent arrangements. This may be very cumbersome to check for the sanity of data after imported in Power BI especially when your data is in bad shape. This may adversely affect your facts and figures and may contribute in raising false flags due to incorrect data. If you have full control on your data then you can easily achieve maximum accuracy level in your reports.

2- Partial Control
When you don’t have much of a control on the way of getting data, you might have to face a lot of challenges in producing quality results. This may be true when organizations are not ready to change the way the data is gathered and produced. This also belongs to change management where organizations are reluctant to experiment new ways because of working culture and limitations in terms of workforce capabilities. In order to minimize effects of inconsistent data format, you need to list down possible variations to cater the same in your queries. Things may be very difficult in cases where consolidation of data is needed. The best way of getting good reports is to ensure that each data provider is ensuring consistent data entry. Proper standards can be enforced when referring similar data like department names and designations where consistent data entry can help in getting correct figures when data is consolidated from different sources.

3- No Control
In cases where you have simply no control on data sources, you have to set expectations correctly with the recipient of these reports. There are always chances of errors especially when data consolidation is required and data is constantly refreshed and updated. Such cases requires constant testing and maintenance if report is not a one off report. Whenever fresh data is imported, proper auditing is needed to check if any real data is ignored by your query rules. This is also true when your report needs data from external/third party sources. If your external source has changed the format of data or moved to a different URL, you will have to go back and fix your reports.