Expression.Identifier

Expression.Identifier is a Power Query M function that generates the M source code representation of an identifier name. The function returns a text value representing the M code.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Expression.Identifier( name as text ) as text

Description

Expression.Identifier retrieves the M source code representation of an identifier , returning it in the properly qualified reference format.

Examples

Understanding how to properly reference step names in Power Query M is important for error free queries. Let’s explore how Expression.Identifier proves useful here.

A Basic Example

Suppose you want to reference a step name called Add custom column. The Expression.Identifier can help you create the right format to reference it. Here’s how:

// Output: "#""Add custom column""
Expression.Identifier( "Add custom column" )

So how does that work?

Basic Reference with Simple Step Names

In the M language, whenever you create a step name it’s important to understand what Power Query does. Let’s say you have a table in a step named Source:

With a simple name like Source you are able reference the identifier (step name) by using its description. For instance, the following code adds a new column to a table called Source.

Table.AddColumn( Source, "Price", each 5, type number )

Rules for Step Names

This direct reference works as long as the step name adheres to certain rules: it must start with a letter or an underscore and can only contain letters, numbers, or underscores. If the step name violates any of these rules, a different referencing approach is needed.

Reference with Special Characters

Consider a step name that includes special characters, such as My/Source. In such cases, Power Query requires a fully qualified reference, which involves using the pound (#) symbol and enclosing the step name in double quotes:

For instance, have a look at the following image:

The formula used is the following:

Table.AddColumn(  #"My/Source", "Price", each 5, type number )

And when you need qualified references in your code, Expression.Identifier is useful to generate the right code. Let’s look at a use case.

Practical use case

Let’s consider a practical scenario where you need to dynamically generate table types based on existing data. Suppose you have a table named Source with columns that include special characters or spaces, such as "HR/Person".

So how could we use such? Suppose you have the following table called myData containing both table names and table types:

First, let’s create a table with diverse column names and types:

#table(
  type table [ Name = text, Kind = text ], 
  {
    {"ID", "number"}, 
    {"DESC", "text"}, 
    {"ID2", "text"}, 
    {"Total Hrs", "number"}, 
    {"Hr/Person", "number"}, 
    {"Date 1", "date"}, 
    {"Date 2", "date"}, 
    {"Date 3", "date"}
  }
)

That gives us the following table:

Assume this table is saved in a step named Source. To create a table type, you need to generate qualified references for column names with special characters. Expression.Identifier can automate this process.

Here’s a step-by-step code example that shows how to use Expression.Identifier to format list values correctly and create a table type:

let
  myData = Source,
  toRecords = Table.ToRecords ( myData ),
  tableElements = 
    List.Transform( 
      toRecords ,  
      each Expression.Identifier ( [Name] ) & " = nullable " & [Kind] ),
  tableTypeString = Text.Combine ( tableElements , ", " ),
  tableType = Expression.Evaluate( "type table [" & tableTypeString & "]" )
in
  tableType

In this example:

• Table.ToRecords(myData) transforms the table into a list of records.
• List.Transform applies Expression.Identifier to each column name, and makes sure to create proper formatting for qualified references.
• Text.Combine creates a single string representing the table type definition.
• Expression.Evaluate evaluates this string to produce the final table type.

This logic that makes sure that all column names are referenced correctly, even if they contain spaces or special characters.

Related functions

Other functions related to Expression.Identifier are:

• Expression.Constant
• Expression.Evaluate

Contribute » | Contributors: Rick de Groot, Antriksh Sharma
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/expression-identifier