Power Bi Matrix & Table Visualizations

Introduction

Here, we will learn about Power Bi most frequently usable visualizations  Matrix & Table.

Description

Matrix & Table both work same to Display Data into Report. The main difference between Matrix & Table are Table Display data Row wise & in Matrix we can display Data Row & Column wise. 

In table we cannot aggregate the Data & Matrix automatically aggregate the data this is one of key feature of Matrix in Power Bi Desktop.

Let's start with example, here we have one sample data with three fields like Customer Name, Customer Segment & Discount. In this data we have multiple entries of same users with different different Discount. Means, Customer Name & Segment are same only Discount is changing for users.

So, if we will display this data into Table visualization, it will show all rows same like below image left side visualization.

And when we display same data in Matrix Visualization, it will aggregate the data automatically and enables the drill down icon same like Right side Visualization.

For Data Analysis purpose Matrix Visualization is good and easy to understand. In Matrix we can transform the Data Column wise as well.

See below screen shot, here we displaying data column wise to using Matrix three property Rows, Column & Value.

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.

How to Import SQL Data in Power Bi Desktop?

Introduction

Here, we will learn what is Import Mode in Power Bi Desktop & how we can import SQL data into Power Bi Desktop.

Description

Mainly, Power Bi Desktop support two types of data source connection mode like Import & Direct Query mode. Here we will talk about Import mode.

In import mode Power Bi Desktop store the data inside Power Bi Cache. If your data size is less then 1 GB or data not continually changing then you can use Import mode. Import mode is very fast compare to Direct Query mode because all data comes from Power Bi Desktop Cache, so you can use this mode to develop & testing purpose. But later on you have to change your data source mode as a Direct Query for production, it will be depends on your organization you want to change this or not.

When you choose import mode, in that case Power Bi fetch the data from given source and load into Power Bi Cache, this is one time activity. Later on you can Schedule or refresh to get latest Data.

So let's start, Open Power Bi file and go to Home menu then click on Get Data & Select SQL Server

After Click on SQL Server one Pop Up screen will appears, here you will write SQL server & Database name. By default Data Connectivity mode is Import.

After that, expand the Advance Option and write SQL query to fetch data for Load.

Then click on OK button, it will fetch data from SQL and asked to you for Load.

After Data Load you can change Data Set name.

Power Bi converts your Table as a Data set and columns as a Fields. Power Bi is very smart, it will assign Datatypes of your fields automatically as per your data behavior. 

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.

How to import Excel file data into Power Bi Desktop?

Introduction

Here, we will learn what is Import Mode in Power Bi Desktop & how we can import & display Excel file data into Power Bi Desktop.

Description

Mainly, Power Bi Desktop support two types of data source connection mode like Import & Direct Query mode. Here we will talk about Import mode.

In import mode Power Bi Desktop store the data inside Power Bi Cache. If your data size is less then 1 GB or data not continually changing then you can use Import mode. Import mode is very fast compare to Direct Query mode because all data comes from Power Bi Desktop Cache, so you can use this mode to develop & testing purpose. But later on you have to change your data source mode as a Direct Query for production, it will be depends on your organization you want to change this or not.

When you choose import mode, in that case Power Bi fetch the data from given source and load into Power Bi Cache, this is one time activity. Later on you can Schedule or refresh to get latest Data.

So lets start, here we will load excel data and under this sheet we have three different sheets like Order, Returns & Users

Now Open Power Bi file and Click on Home Menu. Under Home Menu there is many Options to get data from different different sources, you just click on excel and browse your sheet.

After that it will show your sheets name, here you can choose sheets which you want to load in Power Bi and then click on Load button.

After click on load it will load all data in Power Bi Cache.

After process compilation, you will see three Datasets under Fields Tab. Power Bi converts your sheets as a Dataset and columns as a Fields.

Power Bi is very smart, it will assign Datatypes of your fields automatically as per your data behavior.

Like "Returns sheet" here we storing numeric values in "Order ID" column, so Power Bi changed Data type of "Order Id" automatically as a Integer. 

You can see Sum Icon before Order Id, that's mean you  can perform aggregation on this field. Also you can change the Datatype manually from Power Bi desktop.


Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.

What is Measures in Power Bi?

Measures in Power Bi?

In Power Bi Desktop you can create your own measures to using Data Analysis Expressions (DAX) formula language to perform Aggregation level of Calculations such as COUNTS, AVERAGE, SUM, MAX, MIN & many more.

Why we need Measures?

1. When you have advance & complex level of calculations then you need to create measures. 
2. In power bi desktop there is 200 DAX functions available to using this you can perform complex calculations.
3. Through measures you can easily interact with your report, and it will display under your Dataset Field.

How to Create Measures in Power BI?

1. Right Click on Dataset, then click on "New Measure".
2. After that one formula bar appears just below report menu.

Here, we will create four separate Measures as mentioned below

Min_Unit_price = MIN ( Orders[Unit Price] )

Max_Unit_price = MAX ( Orders[Unit Price] )

Avg_Unit_Price = AVERAGE ( Orders[Unit Price] )

Count_Product_Category = DISTINCTCOUNT ( Orders[Product Category] )

Click on image for Zoom

Now take four Card or Multi Row card as per your choice from Visualization tab then select card and drag your measure into card as shown in above image.

So, Here we learn how we can create & display custom measures in Power Bi.

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always

welcome.

Get Month name from given Date in SQL or DateName function in SQL

DATENAME function :

It will return a specified part of a given date in string format. Here we will get month name from DATENAME function. 

Query as below:

SELECT  DATENAME(month,GETDATE()) as MonthName---Month Name
SELECT  DATENAME(mm,GETDATE()) as MonthName---Month Name

SELECT  DATENAME(m,GETDATE()) as MonthName---Month Name

SELECT  left(datename(month,getdate()),3)as MonthName-- Month Name first three Abbreviation

SELECT  DatePart(mm,GETDATE()) as MonthNumber -- Month Number

Output screen shot as below:-

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.

Use of PIVOT in SQL

Introduction

Here, we will learn how to use PIVOT in SQL. It is very useful technique to transform or display data Row level to Column.

Description with Query

Suppose, we are working on large data and we want to analyze the data year wise and month wise for reporting purpose in that case PIVOT will help us to make it easy.

Help of PIVOT we can transform or display data row level to column level. It is also useful for to make multidimensional reports.

SQL Query as below:

Select * from CustomerData

SQL PIVOT Query:

--Get monthwise customer data

SELECT *

FROM (

SELECT name, mobileno, left(datename(month,dtDate),3)as [month], amount

FROM CustomerData

) as s

PIVOT

(

SUM(amount) FOR [month] IN (jan, feb, mar, apr,may, jun,

jul, aug, sep, oct, nov, dec)

)AS pvt

PIVOT Result:

After execution, query result set screen as above. You can pass year in query then you can get particular year month wise data.

Conclusion

• Pivot help us to transform or display data row to column level.
• It help us to generate Multi-Dimensional report.
• We can generate Month & year wise summary of large data.

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.

How you can set size of your report page? Or Increase Decrease your Report Page Width or height?

Introduction

How you can set size of your report page? Or Increase Decrease your Report Page Width or height?

Description

Suppose you have one report and under that report you have 20 to 30 pages. And all pages layout are not same, so being a developer you cannot display different-different height width pages to users for one report. So you have to set same sizes for all pages.

So now I am going to explain you how you can set size of pages.

Steps are below

Step 1: When you open Power Bi, you will see blank page, and in right side there will be Visualization tab.

Step 2: Under Visualization Tab there is Format pane just below various charts option, see image

Step 3: Click on Page Size, by default page Display Size type is 16:9.

Step 4: Click on custom & set page height & width which you want give.

Step 5: Now go to Power Bi Header Menu, there is View Tab, click on view & you will see three option.   

1. Fit to Page: This is default page setting to fit content in page
2. Fit to Width: It will set page contents & visualization as per your set page size
3. Actual Size: It will display content in full size

Step 6: Choose Fit to Width option.

With some matrix or visualization on page, select one by one all three options and you can easily find out the difference between all three options.

Here, we learnt how we can fixed Page size in Power Bi.

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.