Expression.Identifier

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Expression.Identifier

What is Expression.Identifier function?

The Expression.Identifier function is a built-in function in M language that is used to create unique identifiers for data tables. It takes an expression as input and returns a text value that is unique to that expression. It is commonly used in data transformation scenarios where there is a need to merge or join data tables based on a common key.

The syntax of the Expression.Identifier function is as follows:

Expression.Identifier(expression)

The expression parameter is the input value for which the unique identifier needs to be generated. It can be any valid M expression, such as a column name, a list of values, or a record.

How does Expression.Identifier function work?

The Expression.Identifier function uses a hashing algorithm to generate a unique text value for the input expression. The algorithm takes the input expression, converts it to a binary format, and then applies a set of mathematical operations to generate a hash value. The hash value is then converted to a text value using base 64 encoding. The resulting text value is guaranteed to be unique for any given input expression.

The unique identifiers generated by the Expression.Identifier function are used to join or merge data tables based on a common key. When two or more tables need to be merged, a common key column is identified, and the Expression.Identifier function is used to generate a unique identifier for each row in the key column. The unique identifiers are then used to match rows in the key column of the two tables and merge them into a single table.

Examples of using Expression.Identifier function

Here are some examples of using the Expression.Identifier function in Power Query:

Example 1: Generating unique identifiers for a column

Suppose you have a table with a column named "Name" that contains a list of names. You want to generate unique identifiers for each name in the column. Here is the M code that uses the Expression.Identifier function to achieve this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjQ2VtJRMjQyNjQ2MlK0NzVQyS9XSEzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMjEwMzI3NtLQ0gQqVQjFqVQjNjJywpKMnW0sDSNjJxNU1NzQ0sbm1vYyBnZW5lcmF0ZWQgc2FtcGxlIA==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),

#”Added Custom” = Table.AddColumn(Source, “ID”, each Expression.Identifier([Name]))

in

#”Added Custom”

This code creates a new column named "ID" in the table and uses the Expression.Identifier function to generate a unique identifier for each name in the "Name" column.

Example 2: Generating unique identifiers for multiple columns

Suppose you have two tables, "Table1" and "Table2," that you want to merge based on a common key. The key is a combination of two columns, "ID" and "Date." Here is the M code that uses the Expression.Identifier function to generate a unique identifier for each row in the key column:

let

Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjQ2VtJRMjQyNjQ2MlK0NzVQyS9XSEzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMzRSMjEwMzI3NtLQ0gQqVQjFqVQjNjJywpKMnW0sDSNjJxNU1NzQ0sbm1vYyBnZW5lcmF0ZWQgc2FtcGxlIA==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),

Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjQ2VtJRc03wz0lXKMnWBQgqVQjFqVQjNjJywpKMnW0sDSNjJxNU1NzQ0sbm1vYyBnZW5lcmF0ZWQgc2FtcGxlIA==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),

#”Merged Queries” = Table.NestedJoin(Source1,{“ID”, “Date”},Source2,{“ID”, “Date”},”Table2″,JoinKind.LeftOuter),

#”Added Custom” = Table.AddColumn(#”Merged Queries”, “Key”, each Expression.Identifier([ID] & [Date])),

#”Expanded Table2″ = Table.ExpandTableColumn(#”Added Custom”, “Table2”, {“Value”}, {“Value”})

in

#”Expanded Table2″

This code creates a new column named "Key" in the merged table and uses the Expression.Identifier function to generate a unique identifier for each row in the key column.

The Expression.Identifier function is a powerful tool in Power Query that enables users to generate unique identifiers for data tables. It is commonly used in data transformation scenarios where there is a need to merge or join data tables based on a common key. The function uses a hashing algorithm to generate a unique text value for the input expression, which is then used as the key for merging tables. By using the Expression.Identifier function, users can perform complex data transformations in Power Query and gain valuable insights into their data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)