Power BI Bins or buckets are useful to create a Range of data, similar to SQL Buckets. Let me show you how to create bins in Power BI with an example

  

For this Power BI bins demonstration, we are going to use the SQL Data Source that we created in our previous article. So, Please refer Connect to the SQL Server article to understand the Data Source.

Create Bins in Power BI

To visually demonstrate this Power BI bins, we will create a Table. As you can see from the screenshot below, we created a simple Table by dragging the Product Key and Sales Amount column.

NOTE: I suggest you refer to Create a Table and Format Table article to understand the steps involved in creating and formatting a Power BI Table.

Please select the filed that you want to use as a bin, and right-click on it opens the context menu. Select the New Group option from the menu.

 

Clicking the New Group option opens the following window.

• Name: Specify the Power BI Bin name
• Field: Field or Column that you used for binning
• Group Type: List (group) or bin.
• Bin Type: There are two option such as Size of Bins or Number of Bins
• Min and Max Value: Minimum and maximum value present in the selected Fields. Here, it is the Product Key.

If you selected the Bin Type as Size of Bins, then you have to specify the Bin size (static Number)

Alternatively, if you selected the Bin Type as Number of Bins, then you have to specify the Bin count. Here, Bin size depends upon the Bin count.

Let me select the Bin size as 20 and click OK. It means each bucket size is 20

Now you can see the newly created bin under the Product table.

Let me add that bin to this table. From the below screenshot, you can see that the product Key value is 220 for the keys between 220 to 240

Edit Power BI Bins

Please select the bin that you want to edit, and right-click on it opens the context menu. Select the Edit Groups option from it

Clicking the Edit Groups option opens the following window. Use this window to change as per your requirement.

Delete Bins in Power BI

Please select the bin that you want to delete, and right-click on it opens the context menu. Select the Delete option form it

Click the Delete button to confirm the delete operation.  

 

Create Bins in Power BI Example 2

To demonstrate the Bins, we created a Column chart that shows Color wise sales. This time we create a bin on Sales Amount

Right-click on the Sales Amount, and select the New Group option from the context menu. As you can see from the below screenshot, we defined the Power BI Bin size as the 357.2655

Let me add this new Sales Amount bin to the Column chart legend section.

Let me Edit the Power BI Bin, and change the Bin type to Number of Bins, and Bin count to 3

Now you can see, each bar in the column chart divided by a bin of count 3.

Generally, when you load a table from any data source, Power BI automatically detects the data type of a column. However, there may be some situations where Power BI might get them wrong.

 

For example, it may consider amounts, values, or even dates as the text. In these situations, you can use Power BI change data types of a column option. Let me show you how to Change Data Types of a Column in Power BI with an example.

How to Change Data Types of a Column in Power BI

To demonstrate this Change Data Types of a Column, we are going to use the excel table that we imported in Enter Data article.

To change data types of any column, please click the Edit Queries option under the Home tab.

Clicking Edit Queries option opens a new window called Power BI Power Query Editor.

 

From the screenshot below, you can see Yearly Income, Sales, and HireDate columns loaded as the text.

There are a couple of ways to change data types of columns

First Approach to Change Data Types of a Column

Please select the Column for which you want to change the data type. Next, click on the left corner of the column header (currently it represent ABC text)

Clicking in that position opens a drop-down list of supported data types. Please select the data type that suits your data. Here, we are selecting the Whole number.

Changing the data type of a column opens the following pop up window. Click on the Replace Current button.

Now you can see the Yearly Income header is displaying 123 (whole number) not ABC (text)

Second Approach to Change Data Types of a Column in Power BI

Please select the Column name that you want to alter the data type, and click on the Data Type button under the Home tab

It shows you the drop-down list of available data types. For the demo purpose, we are selecting a Fixed Decimal Number.

This time we will select the Add New Step. It means this change column type add as a new step under the Applied Steps.

As you can see from the screenshot below, it changed the data type of the Sales column and added a new step under the Applied Steps.  

 

Third Approach to Change Data Types of a Column in Power BI

Please select the column that you want to change the data type, and right-click on it will open the context menu. Select the Change Type and then the data type from the list. For now, we are selecting the Whole Number.

Don’t forget to hit the Close & Apply option under the Home tab to apply these changes.

Combine Multiple Tables in Power BI: In real-time, your data is in a normalized format, but in some situations, you might need the de-normalized data. In this situation, you can combine those tables using a query editor. Let me show you how to combine multiple tables in Power BI with an example.

 

How to Combine Multiple Tables in Power BI

From the screenshot below, you can see that there are three tables Product category, Product Subcategory, and Products table. We imported these tables in Connect to SQL article.

For the Combine Multiple Tables in Power BI demonstration purpose, we are going to combine these three tables. To do so, please click the Edit Queries option under the Power BI Home tab.  

 

Clicking Edit Queries option opens a new window called Power Query Editor.

From the screenshot below, you can see that the Product Subcategory table has a connection with both the Product and Product Category table.

 

Before we start combining multiple tables in Power BI, let me duplicate this table by right-clicking the table and selecting the Duplicate option from the context menu.

We renamed it Product Information

Click on the table on any row show you the respective table information.

To combine the Dim Product table, Please click on the right corner of the Dim Product column header. It opens the following window. Use this window to select the required fields from the product table.

We selected the Product Key, Product Name, Color, and Standard Cost from the product table

Now you can see those columns inside the product information table.

To combine the Dim Product Category table, right corner of the Dim Product Category column header. Please select the required fields from the product category table.

We just need the English product Category Name from this table

Now you can see the English product category name inside the Product information table.

Let me rename and rearrange columns, and hit Close & Apply option under the Home tab.  

 

Let me create a table with this newly created table. As you can see from the screenshot below, we also formatted the Table as well.

NOTE: I suggest you refer to Create a Table, and Format a Table articles to understand the table creation and formatting options.

In this section, we show you how to create Power BI clusters with an example. To explain this Clusters concept, we are going to use the Scatter Chart that we created earlier.

 

For this Power BI clusters demonstration, we are going to use the SQL Data Source that we created in Connect to SQL Server article and Scatter Chart.

Create Power BI Clusters

Please click on the … (3 dots) on the top right corner of the chart to see the Clusters option. As you can see from the Power BI screenshot below, we selected the Automatically find Clusters option from the menu.  

 

Clicking the Automatically find Clusters option opens the following Clusters window

• Name: Use this text box to rename the default generated clustered name
• Field: Field or Column that you used for Clustering
• Description: Write a meaningful description that describes this cluster.
• Number of Clusters: By default, Auto is selected. However, you can specify any integer value

For this Power BI cluster demo, we are leaving all the default values. So, let me click the OK button

 

An auto option has generated three clusters

Edit Power BI Clusters

You can edit the cluster in two ways. Under the Visualization tab, click the down arrow beside the Cluster, and select the Edit Cluster option from the menu.

Or, please select the Cluster that you want to edit, and right-click on it opens the context menu. Please select the Edit clusters option from it

Clicking the Edit clusters option opens the following window. Use this window to change as per your requirement.

Let me change the default three to six

Now you can see, it has generated six different clusters

Delete Power BI Clusters

Please select the Cluster that you want to delete. Next, right-click on it and select Delete option from the context menu.

Confirm Delete

Create Manual Power BI Clusters

It is the same as the first example, but this time we are changing the Number of Clusters value from Auto to 5.

and you can see the 5 clusters  

 

Let me use this cluster on another (different) report. For this, we created a Bar Chart using Postal Code Cluster (newly created) and Sales Amount

Like normal columns, you can also Filter Clusters

Let me show you how to Enter Data into Power BI Desktop with an example. In real-time, you might face a situation like manually entering table data, or copying data from the text file to your power bi work environment. In these situations, you can use Power BI enter data option.

 

How to Enter Data into Power BI

To enter new data into the Power BI Desktop, please click Enter Data option under the Home tab.

Clicking Enter Data option opens a new window called Create Table. Here, you can create new columns and add new rows.  

 

By clicking the Column1 area, allow you to add a new name. Let me change the Column 1 header as the Name. Next, click on the * to add a new column.

Now you can see the New column in Power BI.

 

For the demonstration purpose, let me add a few rows

Use the Textbox beside the Name: filed to change the Table name. As you can see from the screenshot below, we changed the table name as Example 1.

Next, click on the Load button to load this table into Power Bi desktop. You can also click the Edit button to edit these fields.

Please wait until the Load completed.

Now you can see the New table

Enter data from Excel file to Power BI Desktop

In this example, we explain the steps to paste the data from Excel file to Power BI desktop. First, let me open the Create Table window by clicking the Enter data button

Let me copy the data present in the Excel file

And paste it in the Create table window (Control + C and Control + V). Remember, Power BI automatically detects the column headers.

Let me rename the table as ExcelEmployee and click the Load button

Now you can see the table  

 

Enter data from SQL table to Power BI Desktop

First, let me copy the data present in the SQL table

and paste it in the create table window

Please rename the table name as per your requirement, and hit the Load button

Now you can see the table

Enter data from Text file to Power BI Desktop

First, let me open the text file. As you can see, the file not formatted properly. Let me copy the content in this text file.

Next, we pasted it in the create table window. You can see from the screenshot below, all the information stored in one column.

We can edit them by hitting the Edit button, but we will write a separate article to explain those techniques. Therefore, for now, we renamed the table name and loaded the table into the desktop.

How to Format Dates in Power BI with an example?. Using these formatting Date options, you can extract Year, Month, Day, Day Number, Day name, Month Start and End, etc.

 

How to Format Dates in Power BI

To demonstrate these Power BI date formats, we are going to use the SQL table that we imported in Enter Data article.

To format dates in any table, please click the Edit Queries option under the Home tab.  

 

Clicking the Edit Queries option opens a new window called Power Query Editor.

From the screenshot below, you can see the HireDate column. To demonstrate the Power BI date formatting options, we are going to use this column.

 

There are a couple of ways to format dates in power bi

The first approach to Format Dates in Power BI

Please select the Date Column, and right-click on it open the context menu. Select the Transform option and select the formatting option. For now, we are selecting the Year. It means this property extract and displays the Years from Hire date.

From the screenshot below, you can see the date and Time in the Hire date column replaced by the Extracted Year.

The second approach to Format Dates in Power BI

First, go to Transform Tab. Under this tab, you can see the Date and Time options. You can use these drop-down lists to format the dates.

Clicking the down arrow beside the Date show you the drop-down list of available date formats. For the demo purpose, we are selecting Days in Month.

From the below screenshot, you can see the date and Time in the Hire date column replaced by the total number of days in a month.

The third approach to Format Dates in Power BI

The above-specified approaches are replacing the existing column with the formatted date. But, this approach adds an extra column for the formatted date.

First, go to Add Column Tab. Under this tab, you can see the Date, Time, and Duration options. You can use these drop-down lists to format the date and Time.

Let me select Date Only option in Power BI.

Now you can see the new column (extra column) displaying the date from the Hire date  

 

Let me Select Time Only

Now you can see the new column displaying the Time from Hire date

Let me select the name of the day

Now you can see the extra column displaying the day name. Next, hit the Close & Apply option under the Home tab to apply these changes.

Please wait until the changed are applied

Now you can see the Date, Day Name, and Time columns under the SQL Employee table.

Power BI Grouping is the process of combining or merging two or more values for further analysis. For example, When we are seeing products by category report, we may find very few records (unnoticeable products). Sometimes, it may be annoying to see all those underperforming products. In this situation, you can create a Power BI group by combining those records, and display it as one product.

  

In this article, we show you how to create groups in Power BI reports with an example. For this Power BI groups demonstration, we are going to use the SQL Data Source that we created in our previous article.

Please refer Connect to SQL Server article to understand the Power BI Data Source.  

 

Create Groups in Power BI

To visually demonstrate this Power BI grouping technique, we will create a Pie chart. As you can see from the below screenshot, we created a Pie chart by dragging the Sales Amount column to Values field, and Color to Legend region.

NOTE: I suggest you to refer Create a Pie Chart, and Format Pie Chart article to understand the steps involved in creating, and formatting a pie chart.

 

If you observe the below screenshot, Data that we highlighted such as Multi, NA, and Blue color products are not performing well. So, let us group them

To perform grouping in Power BI, Please select the fields that you want to group, and right-click on it will open the context menu. Please select Group option from the menu.

Once you Click on the Group option, Power BI will automatically group those items as shown below. That’s it, we did the grouping.

If you observe closely, Legend section is replaced by Color group, and the color is placed in the details section.

TIP: By default, Power BI Group option will group the selected items as one group and remaining options as another group.

Let me remove the Color field from the Details section. Now you can see only two partitions in the Pie chart: one contains Blue, White and NA colours, and the second partition contains remaining colours.

Edit Power BI Group

Please select the group that you want to edit, and right-click on it will open the context menu. Select the Edit Groups option form it

Clicking the Edit Groups option will open the following window.

• Name: Specify the Group name
• Field: Field or Column that you used for grouping
• Group Type: List (group) or bin.
• Ungrouped values: Fields that are not grouped but available in this column.
• Groups and Members: List of groups, and their members.
• Include Other Group: Do you want to place all the ungrouped members in the Other group. If so, checkmark this option.

Let me uncheck the Include Other group. This will remove others group

Now you can see the pie chart that is displaying all the colours, and one group that contains Blue, Multi and NA colours.  

 

Remove Power BI Group Members

To remove Power Bi group members, Please select the group member that you want to remove, and click ungroup button. For the demo purpose, we are removing the Blue colour.

Now you can see that the Blue colour is removed, and added under the ungrouped values

And our pie chart is displaying all the colours, and one group that contains Multi and NA colours.

Rename a Group in Power BI

Double-click on the group that you want to rename, and provide a valid name. Let me rename it as White & Multi & NA

From the below screenshot, you can see that the group name is renamed.

Add a Member to Groups in Power BI

Please select the group member that you want to add, and select the group as well. Next, click on the Group button.

For the demo purpose, we are adding White colour to an existing group.

Now you can see the White colour under the group

Create a New Group in Power BI

Please select the field for which you want to create a group, and click the group button. For the demo purpose, we are selecting the Yellow colour.

Now you can see the new group.

Let me add a few more fields to it

Now you can see, our pie chart has four colours, and they are: Black, Red, and two groups

Once you created a Group in Power BI, it will act like any other column or fields. So, you can use it in N number of charts.

To demonstrate the same, we created a Column chart that shows the Country wise sales.

Next, we added the Color group in the legend section.

The second approach to create Groups in Power BI

Please select the filed that you want to use as a group, and right-click on it will open the context menu. Select the New Group option from the context menu.

This will open the following window

Sometimes, Power Bi will open the same window with the different option like we shown in the below screenshot. But, don’t worry, you can change the Group type from Bin to List

Once you changed that property, you are good to go

 

Power BI Hierarchies provide you with the drill down action to the BI report. Let me show you how to Create hierarchy in Power BI reports with an example.

  

How to Create Hierarchy in Power BI

To demonstrate the creation of hierarchies in Power BI, we are going to use the table that we created in the Combine Multiple Tables article.

There are a couple of ways to create a hierarchy

The first approach to Create Hierarchy in Power BI

To create a new hierarchy in Power BI, Drag and drop one field on to the other. It automatically creates a Hierarchy for you. To demonstrate the same in Power BI, we are dragging the product subcategory onto the product category field.

Now you can see the newly created hierarchy.

  

The first approach to add fields to a Power BI hierarchy

Drag and drop the required field to the newly created hierarchy. Let me drag the color field.

Now you can see the Color field inside the Hierarchy.

The second approach to add fields to a Power BI hierarchy

Please select the field that you want to add (let me select product), and right-click on it will open the context menu. Select the Add to Hierarchy, and then select the Hierarchy name (Product Category Hierarchy)

Now you can see the Product under the hierarchy.

Change Hierarchy Levels in Power BI

Please be careful while configuring the hierarchy levels. For instance, if you have the State column above the Country, then Level 1 is State, level 2 is the Country.

Please select the field that you want to change the position or level. Next, right-click on it and select the Move Up option (or move down) from the context menu. Alternatively, you drag and drop the field at the required position.

Now you  can see the product at the third position

Rename Hierarchy in Power BI

Please select the Hierarchy that you created, and right-click on it will open the context menu. Select the Rename option from the context menu. Alternatively, you can double-click on the hierarchy name

Let me rename it as the product hierarchy

Now you can see the new name  

 

Delete Hierarchy in Power BI

Select the Hierarchy that you delete, and right-click on it open the context menu. Please select the Delete option from the context menu.

Click on the Delete button to confirm the delete operation.

The second approach to create Hierarchy in Power BI

Please select the filed that you want to use in Hierarchy, and right-click on it will open the context menu. Select the New Hierarchy option from the context menu.

It creates a new hierarchy for you.

Let me add a few more fields

Delete Fields from Hierarchy

Please select the field that you want to remove or delete from the hierarchy. Next, right-click on it and Delete option from a context menu.

Click the delete button to delete the Color field from the hierarchy.

You can see the same in the below screenshot

Again, we added the Color field and renamed the hierarchy as the Product Information.

Power BI supports all major joins that are available in SQL. Let me show you how to use joins in Power BI to join multiple tables with an example.

  

The following join types are the Standard join types in Power BI and SQL

1. Inner Join: Returns the rows present in both Left and right table only if there is a match. Otherwise, it returns zero records.
2. Full Outer Join: It returns all the rows present in both the Left and right table.
3. Left Outer Join: It returns all the rows present in the Left table and matching rows from the right table (if any).
4. Right Outer Join: Returns matching rows from the left table (if any), and all the rows present in the SQL Right table.

How to Perform Joins in Power BI

To demonstrate the Power BI join types, we are going to use the tables that we imported in the Load Data from Multiple Data Sources article.

To show the join operations on existing tables, please click the Edit Queries option under the Home tab.

The below Power BI screenshot show you the data present in the Employees table

 

The following screenshot shows you the data present in the Department Table.

To perform Power BI Join operations on any table, we have to use the Merge Queries button present in the Home tab

This drop-down list has two options:

• Merge Queries: It merges the second table inside this original table.
• Merge Queries as New: It creates a new table with the result of joining the first and second tables.

Let  me select the Employees table, and select Merge Queries as a New option because we don’t want to disturb the original table

The following window will open. Use this window to select the Second Table, Join Type, and standard column filed in both the tables.

We are selecting the Department table as the second table.

Now you can see the list of supporting join types.

Left Outer Join in Power BI

For now, we are selecting the Left Outer Join

Next, we selected the DeptID from Employees and Department table as the common fields

Now you can see the new table called Merge1 with all the fields from the Employees table along with one extra filed of values table  

 

Click on the table on any row show you the respective table information

Please click on the right corner of the Department column header. This will open the following window. Use this window to select the required fields from the department table.

Let me select the Department name from the department table, and click OK

Now you can see the Department name merge with the Employee table.

Right Outer Join in Power BI

Let me select the Employees table as the first table, Department table as the second table. And the Dept Id is the common filed, and right outer join as the join type.

Now you can see the new table result of the Power BI right outer join

Full Outer Join in Power BI

Let me select the Employees table as the first table, Department table as the second table. Here, we select Dept Id is the common filed, and full outer join as the join type.

Now you can see the result of Power BI Full outer join in a new table

Inner Join in Power BI

Let me select the Employees and Department tables, Dept Id as the common field, an inner join as the join type

Now you can see the result of Power BI Inner join in a new table

Power BI Pivot Table

Create Query Groups in Power BI

Remove or Reorder Columns in Power BI

How to Rename Column Names in Power BI

How to Rename Table Names in Power BI

How to Split Columns in Power BI

Unpivot Table in Power BI