How to use Power Pivot,Power Query components in Power BI Desktop

What is Power BI?

"Power BI" is one of the most popular Data Visualization tool, and a Business Intelligence Tool.This Power BI tool provides a collection of data connectors,apps, and software services,which are used to get data from a different source, transforms data, and build beautiful reports.

The parts of Power BI

"Power BI" consists of a Windows desktop application called Power BI Desktop, an online SaaS(Software as a Service) service called the Power BI service, Power BI Report Server, allows you to publish Power BI reports to an on-premises report server, and Power BI "Mobile Apps" for Windows, IOS, and Android devices.

This tutorial let's you what is the use of Power BI Desktop and what we can do by using Power BI Desktop.

What is Power BI Desktop?

Power BI Desktop is used to get data from different data sources, we can apply the transformations and build the beautiful reports.

   
This is our Power BI Desktop home screen.

Power BI Desktop consists of three components.

• Power Pivot
• Power Query
• Power View

Power Pivot:

Power Pivot is used to get the data from different data sources like Excel, Flat file, SQL server, SSAS, and Web etc...and make the relation ship between different sources.

This tutorial let’s you how to get the data from Excel, SQL server.

First click on the get data option in Power BI desktop app, it shows common data sources and then click on more option it shows all data sources and we have search option also for search easily select which data source we need, click on required data source and click on Connect.

Excel:

• Now we have to discuss how to get the data from Excel and how to give relation ship between two sheets.
• First click on the get data option, then select Excel data source,then one popup window will display with open button, then choose your excel file where you saved within your PC, after connecting to Power BI then you can select which sheets you want in your excel file, then load your data sheets to Power BI desktop.

• Next if we need to give relation ship between multiple Data Sets

Click on the Model icon on the left corner in Power BI desktop home screen, then we can give relation ship between two or more data sets.

Next we can drag one Object field to another object field(on which fields you want to give relationship) . we can know on which fields you have relation ship just cursor put it on arrow mark and after completing to give relationship then click on Report icon.

we can know on which fields you have relation ship just cursor put it on arrow mark and after completing to give relationship then click on Report icon.

Data Base:

When we click on Data Base option it shows all Data Base connections, then we need to click on your required Data Base and click on connect button.

Next data base related pop window will be displayed and need to give server name and select “Import or Direct Query” and we have advanced option also to connect the data base.

Difference between Import and Direct Query:

Import:

• Entire data will load into model
• If the data source is very large it won’t support(up to 2GB supports).
• It will give better performance because the data is available in model.
• Here we can perform modeling as well.

Direct Query:

• In this model the data won’t store in model only stores Meta data.
• It will support any amount of data
• The performance is low because each & every time need to interact with data base and generate one query and then it will give result.
• Here we can’t perform modeling.

Next need to select which data sets we want from our data base and then click on Load button, finally it will load to Power BI desktop.

Next click on the Model icon on the left corner in Power BI desktop home screen and then we can give relation ship between two or more data sets as discussed in the above.

Power Query:

By using Power Query we can apply any transformations on the Data sets like join the tables, remove duplicates, change the data types, remove columns and replace the values etc...

           

Power Query is an part of Power BI, so there is no need to get Power Query, just click on “Edit Queries” option and you enter the Power Query world.

Now we can start to apply the transformations on the data set.

First we need to select required table on the Power Query window then just right click on your column header in power query window and then displays all below options and by using this options we can change the data in our required style and also we want to remove particular transformation on the right side “Applied Steps”.

This tutorial explains most useful transformations.

Remove:

If we want to remove selected column we need to select on Remove option.

For example i am selecting "EMPNO" to remove from the data set then right click on EMPNO field and click on Remove option and we can see applied transformation on right side on the Power Query window.

       

Remove other columns:

If we want to remove other than selected column then we need to select Remove Other columns option.

Duplicate Column:

If we want copy of existing column then right click on which column you want copy and then click on"Duplicate column".

         

Now I have 17 rows if I have duplicates then I want to remove duplicates.

Remove Duplicates:

If we want to remove duplicates then just click on Remove Duplicates then immediately remove duplicates and we can see our transformation applied or not on the Applied Steps.

Change Type:

If we want to change Data Type to our Field then click on Change Type and need to select what ever data type we want to replace and we can see the Data Type changed or not on the column.

Now i have Whole Number data type on the "EMPNO" column, i want to change Whole Number Data type to Text data type.

Transform:

If we want to change the integer data, text data &other data types data into our flexible manner then we can change click on Transform option.

• We can select like Round,Absolute value,factorial,Power,Square root etc...for Whole number data type.
•  We can select for text transforms Upper case.Lower case,Trim,Length,JSON,XML etc....then immediately change the data in our style.
• We can change Date columns data also by using Transform. 

Replace Value:

If we want to replace the value manually then click on Replace Value option and then give what value you want to change to other value in value to find box and give what value you want to replace with box.

Note: Next you should be give only same data type value other wise it won’t take.

Group by:

Right-click the column header that you want to group on, and click Group By then one popup window will be displayed.

We have two options in Group by Basic & Advance.

• In the Group By popup window, the column name that you right-clicked in step 1 is(by default) selected in the Group By drop-down list. You can select another column name from the drop-down list, if necessary.

• If you want to group on multiple columns, click on advanced radio button and then click on Add grouping button and select a column name from the Group By drop-down list in the newly added row. To remove a grouping column, click on three horizontal dots(…) and click on delete.

In the New column name text box, enter an alias name.

In the Operation drop-down, select aggregate Function.

To aggregate a column, select the column to perform the Aggregate Operation from the column drop-down.

In the Group By popup window, select Sum, Average, Min, or Max as the Operation.

       If you want to aggregate on multiple columns click on add aggregation button.

         Aggregate a column using an aggregate function.

After applying transformations we get the data like below screen shot.

After completing to apply your transformations then click on close & apply option left side on the Power bi.