How to create a table in Power bi – Helpful Guide

In this power bi tutorial, we will see how to create a table in power bi dashboard. And also we will see the below points:

• What is Power bi table
• When to use Power bi table
• How to create table in power bi dashboard
• Create table using DAX Table constructor
• Create table on Power BI with manual value
• Create calculated table in Power BI
• Functions for calculated table
• Create table from another table in Power BI
• Create table from another table with filter
• Power BI create table with measures
• Power BI create table with distinct values
• power bi table formatting
• Power bi table sort by multiple columns
• Power bi table visualization filter
• Power bi table total not correct
• Power bi table column width
• power bi table visual column limit
• power bi table visualiztion limitations
• power bi table visualization total
• power bi table visualization add calculated column
• power bi table visualization not showing all rows

What is Power bi table

• The power bi table is a grid that contains related data in a logical series of rows and columns.
• The power bi table contains header and row totals.
• Power bi table work well with quantitave comparision, when you want to see many values for a single category.

When to use Power bi table

Here we will see when to use the Power bi table. The Power bi table is the perfect choice for:

• See and compare detailed data and exact values in power bi.
• Dislay data in a tabular format in power bi.
• Display numerical data for categories.

Read Power bi measure examples

How to create table in Power bi dashboard

Here we will see how to create a table in Power BI using the Power BI dashboard.

• Open Power bi desktop and Load the data using get data.
• Select the table from the visualization pane
• In the value field, drag and drop the sub-category, quantity, profit, and sales from the field pane.

Now you can see the power bi table contains sub-category, sales, profit, and quantity in power bi.

Read Power BI Slicer between two dates

Power BI Table constructor

A table constructor returns a table having one or more columns in Power BI. It is not a function in a DAX, it is a set of characters. So we can create a table in DAX.

In a table, when there is only one column the name of the column is Value. If there are N no. of columns, then the name of the column is Value1, Value2,…, Value N.

Create table using DAX Table constructor

A table constructor is always surrounded by a pair of curly braces{ }. Now we will see how to create a table using the DAX table constructor.

The syntax of the table constructor is:

{(value1),(value2)}

It means the value1 will be present in the first column and the value2 will be present in the second column of the table.

To implement this go to Power BI Desktop > Modelling > New Table. It will create a new table. Then enter the formula like this:

We can create more rows by separating them with parenthesis(), and comma, For this the syntax should be:

{
  (value1, value2,...),
  (value1, value2,...),
  (value1, value2,...)
}

To implementing this syntax, we have to create another table and enter this formula like this:

This is how to do a table constructor.

Create table on Power BI with manual value

Now we will see how to create a simple table on Power BI Desktop by following these steps:

On Power BI Desktop, go to Home tab > Enter Data.

Here we can rename the column name, table name, and add the column by click on the + symbol. For example, here we will create a table based on Employee Data. The table will look like this:

If we click on the Load, it will directly load on the Power BI. If we click on Edit, it will redirect to the Power Query Editor page.

Now we can use this table to create Data visualization on Power BI.

Create calculated table in Power BI

Here we will see how to create a calculated table by using New Table features in the Report/Data view of the Power BI Desktop.

Step-1:

We have created 2 tables as Group A and Group B.

For Group A the table will look like this:

For Group B the table will look like this:

Step-2:

Now we want to combine two tables into a single table called Group C.

To create a calculated table, in Report view, go to Modelling tab > Under Calculation, click on New table.

Step-3:

Enter the expression in the formula bar. Then press Enter.

Group C = UNION('GroupA', 'GroupB')

We can see this calculated table on the Model view.

This is how to create a Calculated table on Power BI.

Functions for calculated table in Power BI

We can define a calculated table by using any DAX expression that returns a table, including a simple reference to another table. For example:

New Group C = 'Group C'

We can see now another calculated table named New Group C created having the similar data of Group C.

Here are some of the more common DAX table functions we can use:

• VALUES- Returns a one-column table that contains the distnict value from the specified table or column.
• CROSSJOIN- It Returns a table that combines each row of the first table with each row of the second table.
• UNION- Create a join table from a pair of tables.
• TOPN- Returns the top N rows of the specified table.
• INTERSECT- It returns the intersecting of the two tables i.e. it extracts a common row between two tables.

We can get more information or more function from this.

Create table from another table in Power BI

Here is a step-by-step guide, by which we can create a table from another table using DAX expression on Power BI.

By using DAX expression we can create a calculated table. A calculated table is a calculated data derived from a part or whole of the table. We can create the calculated tables by using one or more tables but it must have at least one existing table.

Step-1:

We have loaded another sample data on Power BI Desktop based on the financial report of products having a no. of columns such as sales, cogs, country, profit, Discounts, etc.

Step-2:

On the report page > modeling > New table. Enter the below expression on the formula bar.

Products = SUMMARIZE(financials,financials[Product],financials[Country],financials[Gross Sales],financials[Profit],financials[Date].[Date])

It means it will create a new table as Products having columns as product, country, gross sales, profit, date from the financial data table.

We can see this new data table in the Model view.

This is how to create a table from another table on Power BI using DAX expression.

Create table from another table with filter in Power BI

On Power BI, we can create a new table with filtered data from the existing table. To implement this feature we have to use DAX expression.

For example, here we have already a sample data table i.e. Financial data on Products. Now, we will create another table having filter data from this Financial table.

Step-1:

On the modeling tab, click on the New table under Calculation. To filter the table from another table, the syntax is:

Table = CALCULATETABLE(<Expression>[, <Filter1> [, <Filter2> [, …]]])

Step-2:

Then insert the formula on the formula bar. The expression is:

Filtered table = CALCULATETABLE('financials',FILTER('financials', 'financials'[Country]="Canada"))

Step-3:

We can see the table in Model view having only filtered data as ‘Canada’ in-country column.

Power BI create table with measures

Now we will see how to create a table using a measure in Power BI. To implement this first we have to create a manual table on Power BI Desktop.

Step-1:

On Power BI Desktop, go to Home tab > Enter Data

Here we gave the table name Measure Table and the column name as Product. Then click on Load.

Step-2:

Right-click on the Measure table, select New measure.

Step-3:

Then insert the below formula to create a measure. For example, here we will create a measure that evaluates the sum of the profit(from the financial table)

Total Profits = SUM(financials[Profit])

We can see a measure naming as Total Profits appears on the Measure table.

*Now we can delete the product column from the Measure table. We use this column as it is mandatory to create a table.

Step-3:

We can see the measure on Power Bi using card visuals.

This is how we can organize measures with a measure table on Power BI Desktop.

Power BI create table with distinct values

A DISTINCT DAX function returns a single-column table from a specified column. Also, it removes duplicate values and only showing the unique values.

Here is a step-by-step guide, by which we can create a table using distinct functions.

Step-1:

For example, we have created a Product table based on Products and it’s ID.

We can see that the table has repeated or duplicate values.

Step-2:

on the report page, go to modeling tab > new table. Then insert the below expression:

New Table = DISTINCT('Product Table'[Product ID])

We can see by the use of the DISTINCT function it removes all the duplicate values and only returns the unique values in a single-column table.

Power bi table formatting

Here we will see how to do conditional formatting in the power bi table in the power bi desktop.

We will use the above table visualization for conditional formatting. To apply conditional formatting follow the below steps.

• In the visualization pane, click on the Formatting pane.
• Scroll down to conditional formatting, expand the section.
• We will add the conditional formatting in background of profit column
• Select the profit field from the dropdown, then turn on the background color.
• Click on Advanced control to apply conditional formatting for background color.

• Now background color profit window will open.
• Under format by select color scale and also you can format by Rules and field value.
• Under based on field select profit, then under summarization select sum.
• Now we will use the custom value for formatting, under minimum select custom value write 10,000 and select a color for minimum value.
• Similarly do for maximumn value, add the color and write the custom value 22000.
• Also , you can add the diverging by check the box. Click on apply.

In the below screenshot, you can see the power bi table conditional formatting for background.

• Now we will apply conditional formatting to the font color of the profit column.
• Turn on the font color, then click on the Advanced control.

• Now font color – profit window will open where you can apply conditional formatting.
• Under format by the select color scale and also you can format by Rules and field value.
• Under based on field select profit, then under summarization select sum.
• Now we will use the custom value for formatting, under minimum select custom value write 10,000 and select a color for minimum value.
• Similarly, do for maximum value, add the color and write the custom value 22000.
• Also, you can add the diverging by check the box. Click on apply.

In the below screenshot, you can see the Power bi table conditional formatting apply on the font color.

• Now we will add the data bars and then we will do the condition formatting.
• Turn on the data bars to add the data bars in the profit field of power bi table.
• Then click on Advanced control to apply conditional formatting.

• Now Data bars- profit window will open, If you check the box next to the Show bar only, it will remove the data label and only show the data bars.
• Under minimum select custom value, then write the custom value as -10000.
• Similarly for maximum add custom value and then write the custom value as 22000.
• Choose the color for positive bar and then choose the color for negetive bar.
• Choose the bar direction from the list. Choose the color for Axis. Click on Apply filter.

In the below screenshot you can see the power bi table conditional formatting apply on the data bars.

Read Power bi date filter

Power bi table sort by multiple columns

Here we will see how to sort multiple columns in power bi table visualizations in power bi desktop.

• In power bi desktop, select the table visualization from the visualization pane.
• In the value field, drag and drop the sub-category, quantity, profit, and sales from the field pane.

• Next, we will sort the multiple columns, To sort the Subcategory in descending (z-a) alphabetical order.
• Press shift and click on the column header in the table visual.

• Next, we will sort( desc to asc) the Quantity in the table visual, Click on the shift and click on the column header( shift+ left click).

Read Power BI Aggregate Functions

Power bi table visualization filter

Here we will see how to filter power bi table visualization.

• In the power bi modelling, if you have three table i.e Table, Table1 and Table 2.
• And Table1 and Table2 is single relationship with Table.
• Then in the power bi report, you have created two table visualization, One table visual have data from Table1 and other Table visual have data from Table2.
• Now if you want to filter second table visual by using the 1st table visual, it will not filter because each table visual carry their own data.
• Let’s see how to solve this problem.

Table

Table 1

Table 2

• The relationship between three table, if it is not created, then create the single relationship like below.

• Select table visual from the visualization pane.
• In the value field, drag and drop the ID, value, group, name from Table 1

Now we will create one more table visual which will carry table2 data.

• So, select the table visual from the visualization pane.
• Then in the value field, drag and drop the group, id, name and value.

• If we will filter the second table by clicking A ( name in the first table), it will not filter because both the table visual carry their own data.

• To solve this problem, Select the 1st table visual, and remove the name .
• Then add the name field from the Table.

Now you can filter the second table visual by using the first table visual, click on Name A( first table) to filter the second table visual.

Read Power bi date filter – Complete tutorial

Power bi table total not correct

Here we will see how to show the correct total measure in the power bi table.

• In power bi the issue surrounded using measures in table visualization with total row, so here the problem is total row is wrong for the measure
• But technically, the total row is correct for the measure.
• We expect that Total row is to display the sum of the values in the column, but measure don’t do this.
• In table visual, measure respect the context of the total row and is calculated within that context.
• Therefore the measure used in the table visualization, likely have an unexpected value in the total column.

Here I am using the below sample table to solve this problem.

• Now we will create a measure, which will find the total extra amount spent above 1000 for each year.
• If the amount is not over 1000, then display 0.
• Click on the new measure from the ribbon then add the measure.
• The measure is: Measure1 = IF(SUM(‘Table'[Amount])<1000,0,SUM(‘Table'[Amount])-1000)

• Select the table from the visualization.
• Then drag and drop the Year( year), measure1, and Amount, in the value field.

• Now you can see the total row for measure is 3800, whereas we expect 1500.
• The measure is performing the calculation for all the rows in the table, so the calculation is ( 500+1500+2000+800)-1000=3800.

• To get 1500 as total, for this we will use HASONEFILTER, it will calculate the measure in one way within a row of context and another way within the total row context.
• For this we will create a measure, click on new measure. Then write the measure

Measure2 = IF(HASONEFILTER('Table'[Year]),
IF(SUM('Table'[Amount])<1000,0,SUM('Table'[Amount])-1000),
SUMX(FILTER('Table',[Amount]>1000),[Amount]-1000)
)

• We use the measure 1 in an IF statement that has HASONFILTER function as the logical test.
• If HASONFILTER is true, it will clculate the measure as measure1, if it is false then it will calculate differently.
• Now add the measure to the value field, to show the measure in the table visualization.

Read Power BI Slicer between two dates

Power bi table column width

Here we will see how to adjust table column width.

• In the table visualization, hover over the right of the column header, then double arrow you can see.
• And then click and drag the double arrow to increase the width.

Power bi table visual column limit

Here we will see the power bi table column limit in the power bi desktop.

• The power bi table visual can hold data up to 1 GB.
• And it is not defined how many columns and record can hold, so it is upto the user but the data all together it is upto 1 GB.

Power bi table visualiztion limitations

Here we will see the power bi table visualization limitations.

The limitation of power bi table visualization:

• When applying column formatting in power bi table, you can only choose the one allignment option per column i.e Auto, left, center and right.
• Usually, power bi table contains all text or all number and not a mix.
• In case power bi table contains both text and number, it will automatically allign left to text and right for numbers. And this behaviour support languages where you read from left to right.
• In power bi table, if the text data in the table cell’s or header contain new line character, those character will be ignored unless you toggle on the ‘Word Wrap’ option in the formatting pane.
• In table visual, power bi calculates the maximum cell size based on the first 20 column and the first 50 rows. Beyond those point cell may not be appropriately sized in power bi table.

Power bi table visualization total

Here we will see how to change the summarize for certain columns to average instead of sum.

• Select the table visual from the visualization pane.
• In the value field drag and drop the, sub-category, quantity, profit and sales from the order table in the field pane.

• Now we will change the summarize to average for profit column.
• In the value field, next to the profit click on the dropdown arrow select average.

Now in the below screenshot, you can see the total average of profit in the power bi table.

Read Microsoft Power BI KPI Visual – How to use

Power bi table visualization add calculated column

Here we will see how to add a calculated column in the table visualization in Power BI.

We are using the below sample table to create a calculated column and then create a table visual.

Here we have two columns BeginBAl and YTD amount, we will add these two-column and add that column at the end of the table visual.

• We will create a calculated column, go to modelling and click on the new column.
• Then write the DAX expression: Column = Calculate(sum(‘Table (2)'[BeginBAl])+sum(‘Table (2)'[YTDAmt]))

• Now we will create the table visual, select the table visual from the visualization pane.
• In the value field drag and drop the GIAcc, Description, BeginBal, YTDAmt and Column from the field pane.

In the below screenshot you can see the power bi table visualization add the calculated column.

You may like the following Power BI tutorials:

• How to use Microsoft Power BI Scatter Chart
• Microsoft Power BI Combo Chart
• Power Bi key influencers
• Power BI Donut Chart – How to use
• Power bi gauge chart – How to use with examples
• Power BI combine columns from two tables
• Power BI Date Hierarchy

In this power bi tutorial, we learned how to create a table in the power bi dashboard. And also we discuss the below points:

• What is Power bi table
• When to use Power bi table
• Create table using DAX Table constructor
• Create table on Power BI with manual value
• Create calculated table in Power BI
• Functions for calculated table in Power BI
• Create table from another table in Power BI
• Create table from another table with filter in Power BI
• Power BI create table with measures
• Power BI create table with distinct values
• Power bi table formatting
• Power bi table sort by multiple columns
• Power bi table visualization filter
• Power bi table total not correct
• Power bi table column width
• Power bi table visual column limit
• Power bi table visualiztion limitations
• Power bi table visualization total
• Power bi table visualization add calculated column
• Power bi table visualization not showing all rows