Creating a Table in Power BI Using DAX Table Constructor

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

There are some functions in DAX that are useful in very specific scenarios. For example, sometimes, you might want to create a table entirely in DAX. If you want to do that, what are your ways and how it is possible? This might be helpful especially in the first days of learning about DAX. Let’s see how table constructor can help you to do that. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Table Constructor

Table constructor is not a function in DAX, it is a set of characters which using those, you can create a table in DAX. Table instructor is always surrounded by {} characters. The syntax of table constructor is simple, It is like below:

This means value1 will be the value of the first column in the table, value2 would be the value of the second column and etc.

if you want to have more rows, you can separate them with parenthesis () and a comma ,  like this:

Let’s Experiment

Create a new Power BI Desktop file. And then in the Modeling tab, click on New Table.

In the table expression, you can write:

This will create a table called Sample Table, with one single column called “Value”, and the value in the only row for that would be 1. The Value column automatically takes the data type of the Whole Number.

If you use an expression like this with parenthesis;

You would still get the same output:

If I want a table with two or three rows, I can try this expression:

As you see, even the comma separates the rows. However, what if we want to have more columns? in that case, you need to use parenthesis to bundle them together in one row. like this:

If you want to have multiple rows and columns in the table, you can write an expression like this:

This will create a table with two columns: value1, and value 2, and two rows as below;

Values in each cell can be anything, here is another example:

And Power BI automatically sets the columns data types to Whole Number, Text, and DateTime;

With the table constructor, you can have different data types in each column, but then, Power BI convert all values to a common data type. Like below example;

Limitations

When you are using constructors, you can put any values you like as the values of cells and rows. However, all rows should have the same number of columns. for example, the below expression won’t work:

The second row in this sample is missing the third column:

And that is why you get the error: Each tuple in table constructor must have the same number of columns.

If you want to pass a cell with no value, you can either leave that part blank or use the BLANK() function. Like the below example:

Column names are always Value1, Value2, Value3,…. and you cannot change it in the table constructor. You can, however, change it afterwards with just renaming it, or even using SelectColumns function. Data types of columns are defined automatically, which you can then go and change it manually. Because of these two limitations, personally, I’d rather use the Datatable function in DAX which gives us more options and flexibility. I’ll write about it in another blog article.

Summary

Table constructor is a fast and simple way of creating tables in DAX if you need. However, this method has some limitations on the column names and the data types, which makes the Datatable function a better replacement for that if you want more customization.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply

Your email address will not be published. Required fields are marked *