How to Write a List of Objects in Power Query

Understanding M Language Syntax

In the M language used by Power Query, data types are strictly defined. A common mistake is confusing the syntax for collections with the syntax for individual data structures. You cannot use curly braces {} to define a list; that syntax is reserved for records. Conversely, square brackets [] define a list. Understanding this distinction is critical because Power Query will throw a formula error if you attempt to iterate over a structure it does not recognize as a list.

A record is an unordered collection of fields, similar to a JSON object or a dictionary in other languages. It is defined using curly braces. For example, a single record representing a user might look like this:

{ Name = "John", Age = 30 }

A list is an ordered collection of values. It is defined using square brackets. To create a list of objects, you are essentially creating a list where each element is a record. The correct syntax involves placing multiple record definitions inside square brackets, separated by commas.

Creating a List of Objects Step-by-Step

The most straightforward way to write a list of objects is using the list constructor. This method is ideal when you have a known set of items and want to generate them directly in the Advanced Editor without loading external data sources. Follow these steps to construct your data structure:

1. Open the Advanced Editor: In Power BI Desktop or Excel, go to the Home tab and click on Advanced Editor. This allows you to view and edit the raw M code behind your query.
2. Define the Records: Create each individual object using curly braces. Ensure each field has a unique name within the record. For instance, define a record for an employee with properties like ID, Name, and Department.
3. Wrap in Square Brackets: Enclose all the record definitions within square brackets [ ]. Separate each record with a comma. This tells Power Query that you are building a list, not a single complex record.
4. Assign to a Variable: Use the = operator to assign this list to a variable name, such as MyList.

Here is a complete, syntactically correct example of a list containing three employee records:

let
    Source = [
        { EmployeeID = 1, Name = "Alice", Dept = "Sales" },
        { EmployeeID = 2, Name = "Bob", Dept = "IT" },
        { EmployeeID = 3, Name = "Charlie", Dept = "HR" }
    ]
in
    Source

Note that in the final output, the variable Source holds a list of three records. If you were to expand this column later, Power Query would automatically split these records into separate columns for EmployeeID, Name, and Dept. This pattern is frequently used when you need to merge static configuration data with dynamic query results, a process that often intersects with tasks like Column Rename Excel File Not Reflecting in PowerBI, where structural integrity of the source data is paramount.

Common Syntax Errors to Avoid

Developers often encounter errors because they mix up the delimiters. One prevalent error is writing { record1, record2 }. This creates a record with two fields named after the first and second elements, rather than a list of two records. Another error is forgetting the commas between records, which causes a parsing failure. Always ensure that every record inside the list is terminated by a comma, except for the very last one.

Additionally, be careful with field names. If a field name contains spaces or special characters, you must enclose it in quotes or use the double-quote syntax within the record definition, though standard alphanumeric names do not require quoting. For example, { "First Name" = "John" } is valid, but { First Name = "John" } will fail unless you escape it properly. This precision is necessary because Power Query is case-sensitive and strict about identifiers.

Converting Lists to Tables

Once you have successfully created a list of objects, the next logical step is usually to convert it into a table for analysis. Power Query provides a built-in function called Table.FromRecords specifically for this purpose. By passing your list variable to this function, Power Query iterates through each record in the list and promotes the field names to column headers.

let
    MyList = [
        { ID = 1, Value = "A" },
        { ID = 2, Value = "B" }
    ],
    ConvertedTable = Table.FromRecords(MyList)
in
    ConvertedTable

This conversion is highly efficient and preserves the data types inferred from the values. If you find yourself needing to summarize or aggregate this new table, you might explore techniques for Summarizing max. values for each row in an excel table, which often follows the initial data structuring phase.

Using List.Generate for Dynamic Lists

While static lists are useful, you may sometimes need to generate a list of objects dynamically based on a condition. The List.Generate function allows you to create such lists programmatically. Unlike some lazy evaluation models in other languages, M evaluates expressions eagerly in many contexts, but List.Generate controls the iteration logic explicitly. You provide four functions: one to initialize the state, one to determine if the loop should continue, one to calculate the next value (the record), and one to update the state.

For example, generating five records with incrementing IDs can be done as follows:

let
    Source = List.Generate(
        () => [i = 1],
        each [i] <= 5,
        each [i = [i] + 1],
        each [Record = [i]]
    ),
    Result = Table.FromRecords(Source)
in
    Result

This approach is powerful for creating test data or filling gaps in datasets. However, remember that List.Generate returns a list, so you still need Table.FromRecords to make it tabular. If you are working with complex data relationships, understanding how to handle these generated structures can prevent issues similar to those described in Excel Formula to group similar values, where grouping logic depends on precise data formatting.

Frequently Asked Questions

Can I mix different types of objects in a Power Query list?

Yes, Power Query lists are untyped, meaning you can store records, numbers, text, and even nested lists in the same collection. However, mixing types can complicate downstream transformations like Table.FromRecords, which expects all elements to be records with consistent fields. It is best practice to keep lists homogeneous to avoid type mismatch errors during expansion.

How do I add a new object to an existing list in Power Query?

You can concatenate lists using the & operator. To add a new record, simply append it to your existing list variable. For example, MyList & { NewRecord = "Value" } creates a new list containing all original items plus the new one. This immutable approach aligns with functional programming principles inherent in the M language.

What happens if I forget the comma between records?

Power Query will return a syntax error indicating an unexpected token. The parser expects a comma to separate elements in the list literal. Without it, the engine cannot distinguish where one record ends and the next begins. Always review the comma placement carefully, especially when copying and pasting record definitions.

Is there a performance difference between creating lists manually vs. using List.Generate?

For small datasets, the difference is negligible. However, List.Generate can be more memory-efficient for large sequences because it generates items on demand rather than holding all items in memory at once before processing. For typical business intelligence workloads involving thousands of rows, both methods perform well, but List.Generate offers greater flexibility for conditional generation.

How can I debug a list of objects that isn't expanding correctly?

Use the Value.Type function to inspect the structure of your list elements. You can also expand the list column in the UI to see if all records have the same fields. If some records are missing fields, the expansion will result in null values for those positions. Ensuring uniform schema across all records in the list prevents unexpected nulls in your final table.