Table.CombineColumnsToRecord

Table.CombineColumnsToRecord is a Power Query M function that combines specified columns of a table into a column with records, where each record has field names and values corresponding to the combined columns. The function returns a table that contains the column with records.

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

Syntax

Table.CombineColumnsToRecord(
   table as table,
   newColumnName as text,
   sourceColumns as list,
   optional options as nullable record,
) as table

Argument	Attribute	Description
table		The table containing the columns.
newColumnName		The name of column with the combined values.
sourceColumns		A list of column names to combine.
options	optional	This function supports two options. You can provide them as record: DisplayNameColumn: Designates a column name as the record’s display name. TypeName: Assigns a logical type name to the record, affecting its loading behavior.

Description

Table.CombineColumnsToRecord merges specified columns from a table into a new column. This new column is called “newColumnName”. In this column, values are combined into a record, containing the names and details of the merged columns.

Here are the options you can use with this function:

• DisplayNameColumn: When specified as text, indicates that the given column name should be treated as the display name of the record. This need not be one of the columns in the record itself.
• TypeName: When specified as text, supplies a logical type name for the resulting record which can be used during data load to drive behavior by the loading environment.

Examples

Let’s look at a useful scenario for the Table.CombineColumnsToRecord function.

Creating Custom Data Types in Excel

Imagine you’ve got a list of customers with their details.

#table(
  {"CustomerID", "FirstName", "LastName", "Email", "PhoneNumber"},
    {
      {1001, "John",    "Smith",    "john.smith@example.com",       "(123) 456-7890"},
      {1002, "Emily",   "Johnson",  "emily.johnson@example.com",    "(234) 567-8901"},
      {1003, "Michael", "Williams", "michael.williams@example.com", "(345) 678-9012"},
      {1004, "Sophia",  "Brown",    "sophia.brown@example.com",     "(456) 789-0123"}
    }
)

If you use Excel, there’s this super cool feature where you can mash all the details of a customer into a single cell. So instead of seeing everything sprawled out, you just see the customer’s ID, but all their details are hidden inside that cell. That’s called creating a custom data type.

You can find that option by going to the Transform tab in the ribbon. Then on the right in the Structured Column section you’ll find Create Data Type. Under the hood, this feature makes use of the Table.CombineColumnsToRecord function.

Here’s an example:

Table.CombineColumnsToRecord(
  Source, 
  "Data type", 
  {"CustomerID", "FirstName", "LastName", "Email", "PhoneNumber"}, 
  [DisplayNameColumn="CustomerID", TypeName="Excel.DataType"]
)

With this code, you’ll have a new column called “Data type” that shows just the Customer ID. It’s important to use the TypeName “Excel.DataType” so that Excel recognizes the column as a specific data type.

To inspect the values in the newly created column, you can select any of the cells containing a record Just make sure to click within the cell and not on the text. By doing that a preview pane shows up revealing the hidden record fields and values.

Lastly, just remember: while this works like magic in Excel, it’s a bit different in Power BI. Over there, it just converts columns into a structured record. You can see the entire code in action by pasting below into Power Query’s advanced editor. Remember, creating a custom data type only works in Excel.

let
    Source = #table(
  {"CustomerID", "FirstName", "LastName", "Email", "PhoneNumber"},
    {
      {1001, "John",    "Smith",    "john.smith@example.com",       "(123) 456-7890"},
      {1002, "Emily",   "Johnson",  "emily.johnson@example.com",    "(234) 567-8901"},
      {1003, "Michael", "Williams", "michael.williams@example.com", "(345) 678-9012"},
      {1004, "Sophia",  "Brown",    "sophia.brown@example.com",     "(456) 789-0123"}
    }
),
    MyCustomDataType = Table.CombineColumnsToRecord(
  Source, 
  "Data type", 
  {"CustomerID", "FirstName", "LastName", "Email", "PhoneNumber"}, 
  [DisplayNameColumn="CustomerID", TypeName="Excel.DataType"]
)
in
    MyCustomDataType 

Need more info? Check out this url. It’s shows more details on how to use custom data types.

Related functions

Other functions related to Table.CombineColumnsToRecord are:

• Table.CombineColumns
• Table.PrefixColumns
• Table.SplitColumn
• Table.TransformColumnTypes
• Table.TransformColumns
• Table.TransformRows

Contribute » | Contributors: Rick de Groot, Mahmoud Bani Asadi
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-combinecolumnstorecord