For Power BI version 2.76.5678.782 (December 2019).

Power Query knows several types of objects. Queries can be of type table, list, row or function. Let’s look at how we create different types of objects and convert between them.

Tables

Tables are objects that are most commonly used when working with data in Power Query. We get a table by connecting to various data sources, or we can create one from scratch. New table is created with Enter Data. Power Query offers a plethora of table transformations like adding and removing columns and rows, filtering, pivoting, appending, merging, etc.

Lists

Lists are objects with only one column. Editing lists is similar to editing tables. Lists are commonly used in advanced manipulation of data.

Creating lists

List can be created from a table column by selecting column and Convert to List.

We get a list of the same values as in column. In the field name we now see we have a list. The query icon also shows we’re now dealing with a list.

We can also create a list by entering formula. We select right click, Blank Query.

In the formula bar we enter elements in {} brackets.

• List of numbers:

= {1, 2, 3, 4, 5, 6, 7, 8, 9, 10} or {1..10}

• List of words:

= {“A”, “B”, “C”, “D”}

• Lists are not limited to one data type. List can contain numbers, text or even other lists:

= {1, 2, “R”, “Power BI”, {200, 201, 202}}

Changing list to a table

We can change list to a table by selecting Transform > To Table.

Rows

Lists are vertical entry of data and rows are horizontal entries of data. We can imagine a row as one row of a table, for example one sales transaction. Rows are a bit more complex than lists.

Creating rows

We can create a row object from a table row using the following steps.

1. Select a row in a table. In our example, we want to create a row object from 8th row of a table.

2. Right click and select Add as New Query.

3. We get a new query which returns the value of the field.

4. Delete column name [ID] from formula. The query now returns the entire 8th row of the table.

Counting starts with 0 in M language, meaning Source{0} represents first, Source{1} second and Source{7} eight row.

Changing row to a table

Row can be simply converted to a table by selecting query and then Convert > Into Table.

Functions

Functions are objects we use when we have a repeating process in data manipulation. We save these steps to functions and use them every time we want to edit new data.

Another example of using functions can be found in article Dynamic removal of rows in Power Query for all files in a folder.

Creating a function

Let’s look at a simple example of a function that takes a column of a table and creates a new query with a column of only unique values. We usually use these tables as dimension tables in data model.

1. Select a table and create a reference by selecting Reference.

2. Select a reference and make this query a single column with no duplicates. Select any column, in our case Platform, and remove other columns by selecting Remove Other Columns.

3. Remove duplicates by right clicking, Remove Duplicates.

4. Rename the query, for example function_DimTable.

5. Open Advanced Editor.

6. Here are all the steps of a query. Query is changed to a function by adding ()=> to the start. Arguments or incoming data can be added to brackets and then used throughout the function.

7. In our case we have two incoming arguments, table name and column name. We’ll name them TableName and ColumnName. We add to the start of the function: (TableName as table, ColumnName as text)=>

8. Change the word “Sales” with TableName and “Platform” with ColumnName.

9. Confirm with Done.

10. Query is now a function. We get a view with fields for entering data.

List of all objects

We can see a list of all objects in Power BI project. We get the list by using the following steps.

1. Create a new window by right clicking and selecting New Query > Blank Query.
2. Enter =#shared to formula bar.
3. Change list to table with Into Table.

A list is shown. List contains all the objects in the current Power BI project. List includes all the built in functions. We can explore the functions by clicking on it. Documentation and test window open up.