Creating a Power BI New Table Using DAX Table Constructor Simplified 101

Microsoft’s Power BI is a technology-driven Business Intelligence tool for analyzing and visualizing raw data to present actionable data. It brings together Business Analytics, Data Visualization, and best practices to assist organizations in making data-driven decisions. Because of the capabilities of the Power BI platform, Gartner named Microsoft the Leader in the “2019 Gartner Magic Quadrant for Analytics and Business Intelligence Platform” in February 2019.

A table is a grid that organizes data into rows and columns in a logical order. It may also include headers and a totals row. Tables are ideal for quantitative comparisons involving a large number of values for a single category. A Power BI new table can be created manually and also by using a DAX Table Constructor.

This article describes how a Power BI New Table can be created using DAX Table Constructor and also gives an idea of Power BI and DAX and their key features.

What is Power BI?

Power BI is a proprietary Business Intelligence tool designed for seamless Data Analytics and Data Visualization. It is a part of the Microsoft Power Platform. Power BI is one of the common tools used by organizations for analyzing their business data construct reports. 

It comes with a collection of various in-built software services, apps, and connectors that deeply integrates with data sources to deliver immersive visuals, interactive reports, and generate insights. Power BI uses its advanced charts, graphs, and other visuals included with Machine Learning to easily extract valuable information out of data.

Power BI can also read data from XML files, CSV files, JSON format files, and even web pages, then convert raw data into interactive insights. It is available for Desktop, mobile, and on-premise servers. Users can create and share their reports with other Power BI users within the organization or partner companies. 

Power BI has a number of significant advantages, including:

• Data Visualization in real time
• Using Cortana, create charts and generate reports on the fly.
• Large storage capacity and straightforward Data Retrieval.
• Row-level Security is improved.
• BI has a simple User Interface and remembers the most frequently used trends.

Key Features Of Power BI

Some of the main features of Power BI are listed below:

• Supports API Integrations: Power BI allows developers to easily integrate with other applications and embed dashboards into other software using sample codes and APIs.
• Custom Visualization: Power BI offers custom visualization libraries support that allows users to visualize complex data with ease.
• AI Support: Users can easily perform Data Analytics using Artificial Intelligence. With the built-in AI support, users can prepare data, build Machine Learning models, and gain insights.
• Modeling View: With the help of Modeling View, Power BI users can slice and divide the complex data into simpler ones that help in better understanding the data, separate diagrams, and multi-select objects.
• Easy Sharing: Power BI makes it easier for users to easily share their reports within teams, or organizations ensuring full data protection.
• Hybrid Development: Power BI easily integrates with many 3rd party connectors, applications, and services widely used by organizations that allow users to connect to various data sources.

To know more about Power BI, click here.

What is Power BI Table?

A Power BI Table is a grid with related data organized in a logical series of rows and columns. The header and row totals are included in the Power BI Table. When you want to see many values for a single category, a Power BI Table works well with quantitative comparison.

The Power BI Table is ideal for viewing and comparing detailed data and exact values in Power BI, as well as displaying data in a tabular format and numerical data for categories. There are various functions in DX used to create a Power BI new table or make changes in an existing table.

In Power BI, a table is a network that contains related data in a logical order of lines and segments. It could also include headers and a line for totals. Tables work well with quantitative correlations when you’re looking at a lot of different qualities for a single classification. 

What is DAX?

DAX (Data Analysis Expressions) is a Power BI functional language that uses a collection of functions, operators, and constants to solve basic calculation and Data Analysis problems. Analysis Services, Power BI, and Power Pivot in Excel all use Data Analysis Expressions (DAX) as a formula expression language. In tabular data models, DAX formulas include functions, operators, and values that can be used to perform advanced calculations and queries on data in related tables and columns.

Working in DAX improves the user experience by implementing functionalities such as Data Visualization, Data Importing, and Manipulation. Basic dashboard knowledge is sufficient for creating standard reports, but DAX is required to create sophisticated and insightful reports. The reports generated are highly intuitive and discerning, thanks to the numerous commands and measures that can be used under the DAX syntax.

DAX is a formula language used in Power BI Desktop to work with relational data. DAX comes with a library of over 200 functions, operators, and constructs, giving you a lot of freedom when it comes to creating formulas to calculate results for almost any Data Analysis task. Calculated tables are ideal for intermediate calculations and data that you want to keep in the model rather than calculating on the fly or as query results. For example, you could join two tables together using a union or a cross join.

Some of the functions that either return a table or manipulate one that already exists in DAX are ADDCOLUMNS, CROSSJOIN, CURRENTGROUP, DATATABLE, EXCEPT, FILTERS, DISTINCT table, Table Constructor, UNION, VALUES, etc. 

The majority of the time, you create tables by importing data from an external data source into your model. Calculated tables, on the other hand, allow you to create new tables based on data that has already been loaded into the model. Instead of querying and loading values into your new table’s columns from a data source, you define the table’s values using a DAX formula.

Key Features Of DAX 

• Standard Functionality like Formulas in Excel: SUM, SUMIF, VLOOKUP, and other Excel formulas are familiar to most users. In Power BI, DAX provides similar formulas and additional functionality for all types of calculations. Nonetheless, Excel formulas operate on cells, whereas Power BI operates on tables and columns. For more information on each of the more than 200 functions available in DAX, click here.
• Simplicity: There is a learning curve to grasp the concepts and ideas of DAX, but it is doable, and after a short period, you will notice a structure and logic. It’s very simple once you’ve grasped these fundamental concepts.
• Solves Business Problems: Whenever you have a data problem in Power BI, there’s a good chance DAX will solve it. In DAX, you can perform a wide range of calculations, and you can always search for something in DAX using a search engine or the Power BI community.
• Improves the Data Model: You must look after your underlying data model if you use DAX. Data modeling is sometimes regarded as an art form, but once you grasp the fundamental concepts, such as dimensional modeling and star schemas, your data model’s maintainability will greatly improve.
• Time-intelligence Functions: These functions aid in the creation of calculations involving calendars and dates. This allows you to make meaningful comparisons between periods (e.g. calculations of year-to-date, comparison with last period, previous month, etc.).
• Calculated Columns: Eventually, you’ll be in a situation where your data lacks a field that you require. Calculated columns can be used to define the value of a column, for example, by combining text values or calculating a numeric value from other values.

Understanding the Key Aspects of Power BI New Table

In DAX, some functions are useful in very specific situations. For example, you might want to create a table entirely in DAX on occasion. You can do this using a Table Constructor. Here are a few key aspects of Power BI New Tables that can come in handy:

• Understanding Power BI New Table: What is Table Constructor?
• Understanding Power BI New Table: Creating Power BI New Table Using DAX Table Constructor
• Understanding Power BI New Table: Limitations of Creating Power BI New Table Using DAX Table Constructor

Understanding Power BI New Table: What is Table Constructor?

A Table Constructor returns a table with one or more columns. It is used to create a Power BI new table.

The Table Constructor is not a DAX function; it is a collection of characters that can be used to create a table in DAX. The Table Constructor is always surrounded by {} characters. When there is only one column in a table, it is called Value. If there are N columns, the column names are Value1, Value2,…, Value N.

The table constructor’s syntax is as follows:

{<value1>,<value2>...}

This means value1 will be the value of the table’s first column, value2 the value of the second column, and so on.

If you want to have more rows, use parenthesis () and a comma to separate them, as shown here.

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

 Understanding Power BI New Table: Creating Power BI New Table Using DAX Table Constructor

To implement Power BI New Table Using DAX Table Constructor, follow the steps below:

• Step 1: Make a new file in Power BI Desktop. Then select New Table from the Modeling tab.

• Step 2: You can write the following in the table expression:

 Sample Table = {1}

This will create a table named Sample Table with a single column called “Value” and a value of 1 is the only row. The data type of the Value column is set to Whole Number automatically. 

You get the output shown below:

•  Step 3: Try the following expression if you want a Power BI new table with two or three rows:

Sample Table = {1,2,3,4}

•  Step 4: Even the comma separates the rows, as you can see. However, if you want to have more columns, you must use parenthesis to group them in a single row, as shown below:

Sample Table = {(1,2,3,4)}

•  Step 5: You can use an expression like this to create a Power BI new table with multiple rows and columns:

Sample Table = {(1,2),(3,4)}

This will result in a table with two columns: value1 and value2, as well as two rows, as shown below.

•  Step 6: Each cell’s value can be anything; here’s another example:

Sample Table = {
  (1,"the first row",DATE(2019,1,1)),
  (3,"the second row",Date(2020,5,12))
 }  

Furthermore, Power BI sets the data types of the columns to Whole Number, Text, and DateTime by default.

• Step 7:  You can have different data types in each column using the Table Constructor, but Power BI will convert all values to the same data type. As an illustration, consider the following:

Sample Table = {
 (1,"the first row",DATE(2019,1,1)),
 (3,"the second row",12),
 (3,"the second row","something")
}

Understanding Power BI New Table: Limitations of Creating Power BI New Table Using DAX Table Constructor

• When you use Constructors, the values of cells and rows can be any values you want. However, the number of columns in each row should be the same, or else it shows an error.
• Value1, Value2, Value3,…. are always the column names, and you can’t change them in the Table Constructor. You can change it later by simply renaming it or by using the SelectColumns function. Column data types are defined automatically, and you can then change them manually.

Because of these two drawbacks, it is preferred to use the DAX Datatable function, which provides you with more options and flexibility.

Conclusion

This blog talks about creating a Power BI new table using DAX Table Constructor and also gives a brief introduction to Power BI and DAX. If you need to create tables in DAX, the table constructor is a quick and easy way to do so.

Power BI is a great tool for performing Data Analytics and Visualization for your business data. However, at times, you need to transfer this data from multiple sources to your PowerBI account for analysis. Building an in-house solution for this process could be an expensive and time-consuming task. Hevo Data, on the other hand, offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc.

This platform allows you to transfer data from 150+ sources to BI tools like Power BI, and Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

FAQs