Tag Archives: Power BI

ByHariharan Rajendran

Explore Power BI Report Server

Microsoft Power BI team releases a Power BI Report as generally available. We can get this report server as part of Power BI premium but still, we can use the trial version for 180 days.

This post explains to you that how we can setup the Power Bi report server. You can download the Power Bi Report Server here.

Components

It contains two components

  1. PBIReportServer.exe
  2. PBIDestopRS (32 & 64 bit)

Installation

It is easy to install Power BI Report server in your system. Just follow the below screenshots and click Next and complete the installation.

Once the installation is completed, we need to configure the Report Server which is nothing but our regular SSRS report server configuration but for Power BI.

Configure as usual and test the web service and web portal URL.

To start developing the reports, need to install the Power BI Desktop RS which is same like regular Power Bi desktop but with few changes.

We can save the report in our on-premises portal with the PBIDesktop RS tool.

Check the general information about Power BI Report Server.

Report Server Power BI Report Server
Instance ID PBIRS
Product Version 14.0.600.286
Service Power BI Report Server
Log on Account NT SERVICE\PowerBIReportServer
ByHariharan Rajendran

R Packages in Power BI

Power BI service supporting many R packages as of now and it will increase in future. There is a misunderstanding that we can use these packages in Power BI Desktop R visual.

We can’t use all the packages in the Power BI site because it includes the packages which can be used for R custom visuals development.

Check the list of packages that Power BI Service supports, here.

Let us take an example,

There is a package called “timevis” which is part of Power BI supported packages.

We can use this package to build a visual to display a time range information. I used this package in R studio and got the below visual.

 

You can use the below R script and try it by yourself. This package requires many dependent packages which you can identify while installing the R scripts. If any dependent packages are missing then you should install those missing packages and finally run the R script.

library(timevis)

input<- data.frame(

           id = 1:4,

      content = c(“Apple”, “Orange”, “Mango”, “Banana”),

        start = c(“2017-01-10”, “2017-01-11”, “2017-01-20”, “2017-02-14 15:00:00”),

          end = c(“2017-02-13”, NA, “2017-02-04”, NA)

                  )

timevis(input)

It is easy to identify which packages are supported in Power BI from the list. Prepare your R script from any of the packages and make sure to check the supported package list and run the script in R studio.

If your package producing a visual and make sure that visual is displayed under plot section of R studio. If you see the visuals in plot section then those packages can be used in Power BI.

In our example, the visual is displayed under viewer section which means it is using HTML viewer which is not yet supported by R visual in Power BI.

ByHariharan Rajendran

Power BI Report Server

Microsoft SQL Server Reporting Services is the product which provides solutions to all the business enterprises. While it was in peak, Microsoft released another new BI tool which is nothing but Power BI.

Now Power BI is leading the Business Intelligence Industry. As it is a cloud-based service, business users were not able to use that tool for their business solutions because of security concerns. They were waiting for on-premises based Power BI server.

Now the wait is over. Microsoft released Power BI Report server which can be installed and configured for on-premises business solutions.

Power BI Report server is available for preview, you can download, install and experience the on-premises based Power BI Report.

As it is very new, it supports only analysis services as a source but in future, we can expect the same source connectors as like in Power BI cloud services.

This blog post explains to you that how you can experience the Power BI Report server using the Azure VM template.

Follow the below steps to spindle up Azure VM with Power BI Report Server on it.

Step 1: Login Azure portal and add a new Virtual machine

Step 2: Search for Power BI and it will show the below template

Step 3: Click create

Step 4: Add the VM name and credentials and choose which AS server you want to use from the drop down.

Step 5: By default, it using the below settings. In case, if you get an error message then change the name of the storage.

Step 6: At last, you need to click the purchase button. It will create and deploy the Power BI machine.Once the machine is deployed successfully, connect the machine and you can see the Power BI.

Once the machine is deployed successfully, connect the machine and you can see the Power BI Report Server.It also adds an option to create Power BI Report from the portal itself.

ByHariharan Rajendran

Date Table in Power BI – M Language

Power BI Desktop supports Power Query Language which is nothing but M language for data modeling.

This article explains how we can create date table with all the necessary columns. Generating date table is easy in Power BI as we have different methods.

Refer my below article to generate date table manually or with single DAX query.

Date table in Power BI

Use the below M query to create a date table,

//Create Date Dimension

(StartDate as date, EndDate as date)=>

let

    //Capture the date range from the parameters

    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),

    Date.Day(StartDate)),

    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate),

    Date.Day(EndDate)),

//Get the number of dates that will be required for the table

    GetDateCount = Duration.Days(EndDate – StartDate),

//Take the count of dates and turn it into a list of dates

    GetDateList = List.Dates(StartDate, GetDateCount,

    #duration(1,0,0,0)),

//Convert the list into a table

    DateListToTable = Table.FromList(GetDateList,

    Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),

//Create various date attributes from the date column

    //Add Year Column

    YearNumber = Table.AddColumn(DateListToTable, “Year”,

    each Date.Year([Date])),

//Add Quarter Column

    QuarterNumber = Table.AddColumn(YearNumber , “Quarter”,

    each “Q” & Number.ToText(Date.QuarterOfYear([Date]))),

//Add Week Number Column

    WeekNumber= Table.AddColumn(QuarterNumber , “Week Number”,

    each Date.WeekOfYear([Date])),

//Add Month Number Column

    MonthNumber = Table.AddColumn(WeekNumber, “Month Number”,

    each Date.Month([Date])),

//Add Month Name Column

    MonthName = Table.AddColumn(MonthNumber , “Month”,

    each Date.ToText([Date],”MMMM”)),

//Add Day of Week Column

    DayOfWeek = Table.AddColumn(MonthName , “Day of Week”,

    each Date.ToText([Date],”dddd”))

in

    DayOfWeek

 

It asks for start and end date. Once entered the date then click Invoke, it will generate the query.

ByHariharan Rajendran

DAX Leap Year In Power BI

Identifying the Leap is very simple logic, this post explains that how you can identify a year which is a leap year or non-leap year in Power BI.

Consider I have a year column on my table. If not then use the Year function on the data column and get the year column.

You need to find the below values,

  1. Year Start Date
  2. Year End Date
  3. Days between Year Start Date and Year End Date.
  4. Check the number of days

That’s it, very simple.

Use the below DAX expressions to identify the above values.

YearStartDate = DATE(‘Year'[Year],01,01)

YearEndDate = DATE(‘Year'[Year],12,31)

TotalDays = (‘Year'[YearEndDate]-‘Year'[YearStartDate])*1+1

IsLeapyear = IF(‘Year'[TotalDays]=366,”Yes”,”No”)

Happy Learning.

ByHariharan Rajendran

Power BI Pre-defined Transformations

This article talks about the pre-defined transformations that we can directly use in Power BI Desktop. These transformations are part of Power Query Editor.

As a business intelligence developer, usually we spend most of the time to prepare the dataset for report and dashboards. Preparing the dataset includes, getting the data from different sources, data modelling and transform the data.

Data transform is applicable for both rows and columns. Check the list of pre-defined transforms in Power BI Desktop.

These transforms are grouped into different categories.

Table Level

To add a new column

To play with the columns,

To format a text column,

To format a number column,

Date and Time columns,

The above sections are specific to columns. To play with rows,

There any many situations where we need to combine the tables and queries. To handle those scenarios, we can use the below options in Power BI.

To sort the columns,

We also have R script which we can use to perform any complex transform or logics with the data.

Apart from the pre-defined options still we can do complex logics with M language (Power Query Language).

Happy Learning & Share your comments.

ByHariharan Rajendran

Date table in Power BI

Every BI Solutions will have a date dimension table data with other business data. Date dimension is one of the mandatory table as most of the analysis are based on date dimension table.

We can generate the date dimension table in all the scenarios. This post explains you that how we can generate the date dimension table in Power BI.

We have different ways to generate date dimension table. Here I explained two different ways.

Create a table by creating individual column manually

Use New table option and start to create the columns with below expressions.

Column Name Expressions
Date CALENDARAUTO()
DateAsInteger FORMAT ( [Date], “YYYYMMDD” )
Year YEAR ( [Date] )
Quarter “Q” & FORMAT ( [Date], “Q” )
YearQuarter FORMAT ( [Date], “YYYY” ) & “-Q”

& FORMAT ( [Date], “q” )

Monthnumber FORMAT ( [Date], “MM” )
YearMonthnumber FORMAT ( [Date], “YYYY/MM” )
YearMonthShort FORMAT ( [Date], “YYYY/mmm” )
MonthNameShort FORMAT ( [Date], “mmm” )
MonthNameLong FORMAT ( [Date], “mmmm” )
DayOfWeekNumber WEEKDAY ( [Date] )
DayOfWeek FORMAT ( [Date], “dddd” )
DayOfWeekShort FORMAT ( [Date], “dddd” )

Single Script to Generate Date Table

Add the below script in New table expression.

DimDate =

VAR fiscal_year_end_month = 3

RETURN

    ADDCOLUMNS (

        CALENDARAUTO ( fiscal_year_end_month ),

        “DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),

        “Year”, YEAR ( [Date] ),

        “Quarter”, “Q” & FORMAT ( [Date], “Q” ),

         “YearQuarter”, FORMAT ( [Date], “YYYY” ) & “-Q”

            & FORMAT ( [Date], “q” ),

        “Monthnumber”, FORMAT ( [Date], “MM” ),

        “YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),

        “YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),

        “MonthNameShort”, FORMAT ( [Date], “mmm” ),

        “MonthNameLong”, FORMAT ( [Date], “mmmm” ),

        “DayOfWeekNumber”, WEEKDAY ( [Date] ),

        “DayOfWeek”, FORMAT ( [Date], “dddd” ),

        “DayOfWeekShort”, FORMAT ( [Date], “dddd” )

    )

The above script will generate a table with all the columns. We can customise the table by modifying the expression.

ByHariharan Rajendran

DAX Query CALENDARAUTO()

In Power BI, you can use the pre-defined function CALENDARAUTO() to create date table with a column called “Date”. It will generate a date for current 1 year by default.

This above function will only work when you have a model with at least one column with date or datetime data type.

In the below screenshot, I tried to use CALENDARAUTO() function in an empty model.

You can see the warning message above.

“CALENDARAUTO() function can not find a base column of DateTime type in the model”

In the same scenario, I just added a simple table with one date data type column then I used the CALENDARAUTO() in a new table and this time I got the expected result.

Happy Learning. Share your comments.

12