This tutorial will discuss about table value. It’s one of the most common structured values that you can encounter in many Power BI data reports. You’ll learn how to build tables using different expressions in order to obtain specific information and make your report insightful.
Creating A Table Value
Think of tables as a list of records. The hash or pound table function can be used to construct a table value from a list of column names and a list of the record field values.
To create a table, create a new query by right-clicking the space below the Query pane. Click New Query and then select Blank Query.
Input the following code and press Enter. You can then see the table icon beside the query name inside the Query pane.
If you want to create a number of columns without specific column names, you can enter a number as the first parameter followed by a list of record field values.
The formula created 5 columns with 2 records. The two records are lists with values from 1 to 5, and 6 to 10 separated using a comma. If you input 4 values instead of 5 in the second record, you’ll get an error.
If you click the space beside the error, you’ll get an error message saying 5 keys were specified in the formula yet only 4 values were provided.
But if you change the number of columns to 4 and press Enter, the first record now returns an error.
And when you click the space beside the error, you’ll see an error message saying 4 keys were only specified yet 5 values were provided.
Setting Data Types Of Columns
Most of the time when constructing a table, you want to include the column names. In this syntax, you can see that the column names are a and b. You can also see two records with values 1 and 2, and 3 and 4.
You’ll also notice that column icons have ABC123. It’s because the lists with record field values can obtain both primitive and structured data types.
It’s possible to declare data types when constructing a table. The first parameter will no longer be a list of column names, but a declaration of a table type that includes both column name and the column type.
In the formula, the first column is called a and has a number type. The second column is called b with a text data type. There are also 3 record values containing a number and a text. You can also see each column icons with their associated types.
If you change the field value of the second record from {2,“two”} to {2,2}, you won’t get an error message and the field name two will be changed to 2 in the column. Even though 2 is a number, there is no type validation occurring. However, if you pass this field into a function that expects a text value or load this query to the data model, it will be evaluated and a mismatch error will occur.
Accessing Items From A Table Value
There are other ways to create tables. You can use M functions that return tables from lists or records, or you can manually add a table using the Enter Data option on the Home tab. But most of the tables that you’ll be dealing with inside Power Query are the results of connecting to an external data source.
When it comes to accessing elements from a table, you can access both rows and columns by referring to its zero-based index position. You can use the positional index operator, which is a set of curly brackets ({ }).
If you want to access the first item in the sample table above, input curly brackets at the end of the formula and write 0 inside the brackets. The formula will then return the first value.
Accessing the first item in a table returns the entire row as a record value. You can also perform the optional item selection by adding a question mark. This changes the not-found behavior from returning an error into returning a null.
So if you want to access the fourth item, change the index operator to 3 and press enter. Then, add the question mark at the end of the formula. The syntax will then return a null.
Tables also support field selection, which is the field name in square brackets.
The syntax returns the first column by adding square brackets at the end. Column a is then written inside the brackets to pull out the first column.
A column can contain more than one value so this returns a list in an ordered sequence of values.
Combining And Equating Tables
Combination and equation operators can be used with tables. Tables can be appended using the combination operator, ampersand (&).
You can compare tables with the equal or not equal sign. It can be helpful to remember that a table is a list of records. Tables are considered equal if they meet all four criteria:
- They have the same number of columns.
- They have the same number of rows.
- All column names or record field names are present and equal in both tables.
- All record field values match.
Here is an example:
The formula contains two tables with two columns each. The first table has columns a and b, and values 1 and 2. The second table has columns b and a, and values 2 and 1. This formula yielded TRUE because the order of the field or column name is irrelevant when comparing tables.
***** Related Links *****
Tables In Power BI: Types & Distinctions
Creating Measure Tables & Subfolders In Power BI
Connecting Data Tables – Tips On Power BI Relationships
Conclusion
Most Power BI reports have tables that consist of various data inside rows and columns. These tables are the main data-generating entities inside Power BI. They show information in a table form, which makes your reports look compelling.
Melissa
