Tag Archives: Power BI

Split column into rows

A brilliant new feature is now available in Power BI – Split column into rows. To understand why we need it, you must go and read the article  – Analyzing badly captured Survey data or feedback forms. This method used Power Query concepts of Split and Unpivot. Now these have been combined into a single, intelligent command called Split columns into rows.  It sounds confusing at first. But soon you will realize that it is an amazing tool.  Learn it just 4 minutes.

Raw data looks like this

Split column into rows - raw data

And you get a report like this. No need to use formulas or do any manual work.

Split column into rows - Final Charts

You must have the May 2017 update for Power BI Desktop installed.

Continue reading Split column into rows

Power BI Dates before 1900

This is a short post. It is like an FYI mail. Excel never understood any dates before 1900. We got used to that limitation over the decades. But Power BI does understand Dates before 1900. The best part is, you do not have to take any specific action. It just works.

Here is the raw data and the Power BI output.

Dates before 1900 - raw dataDates before 1900 - in a chart

If you try this in Excel, it just will not work. Now that you know this, starting using Power BI with Dates before 1900.

Mind you, the Power BI documentation says that the earliest limit is 1900. It still works for dates before 1900. Drill down is also supported. Here is the same data at Day level.

Drill Down

This ability  may make historians and archeologists partially happy. There time scales are huge and Power BI does not support that much of a range. But still, it is an improvement worth knowing about.

Quick Calc in Power BI

Quick Calc in Power BI (Desktop as well as Portal) is a handy feature. Similar to Show Values As in Pivot Tables.

How to use Quick Calc in Power BI

When you add a measure in the value area, it can be shown as % of Grand Total. Just open the dropdown next to the field and choose Quick Calc.

Quick Calc in Power BI - menu option  Quick Calc in Power BI - Dialog

In the dialog, choose the summarization as desired and then choose Percent of of grand total option from the Show Value As dropdown.  Here is the result. I have added the same column twice. Once to show the actual and other as % of total.

image

With little bit of tweaking of formatting options in Power BI designer, nice looking reports can be created. One of the most common complaints is that the default font size is too small. That can be changed by going to the General section of formatting area.

Font options in Power Bi

The Show Values As dropdown has only one option as of now. Of course, it is nothing compared to the Pivot Table Show Values As capability – which offers  14 options. I am sure more options will be added to Power BI soon.

Eliminates a measure

Without Quick Calc in Power BI, you would have to create a measure for the % Total with the following syntax.

%ofTotal =
SUM ( Query1[Likes] ) / CALCULATE ( SUM ( Query1[Likes] ), ALLSELECTED () )

Or better still, to avoid DIV/zero error in a single command:

%ofTotalBetter =
DIVIDE (
    SUM ( Query1[Likes] ),
    CALCULATE ( SUM ( Query1[Likes] ), ALLSELECTED () ),
    0
)

The base formatting in Power BI itself is rather primitive at this point of time. This code was formatted using the amazing tool created by Alberto Ferrari and Marko Russo called Dax Formatter. You just copy paste valid DAX code there and it formats it automatically. Output can be an image, HTML or Word. API also available.

Power BI Udemy Course coming very soon

Power BI is becoming more and more powerful day by day. It is time everyone got to know about it. It is the next natural progression from Pivot tables. It is not just easy to use. It gives you more capabilities which you have never had before – and that too with lesser effort.

As promised, the detailed Power BI course is almost ready. It will be released in the near future. Watch this place for further announcements.

 

Convert Power View dashboards to Power BI Desktop

Although Power View and Power BI reports are similar, Microsoft is focusing on the later. Power BI platform is being separated out from Excel for many reasons. One of the reasons is to eliminate the dependence upon Silverlight (technology used to create Power View, which is now being phased out).

Here is a 2 minute video which explains how to convert Excel based Power BI components like data mode, queries, relationships and Power View dashboards into Power BI Desktop. Once converted, you can publish these to the Power BI portal.