Edit

ColumnNames and Column functions

  • Article

Applies to: Canvas apps Model-driven apps Power Platform CLI

Retrieves column names and values from an untyped object.

Description

The ColumnNames function returns the names of all columns from an untyped record, returning a table with all the names from that record.

The Column function returns the value of a property from an untyped record with the given column name. The value is returned as an untyped object.

The ColumnNames and Column functions return errors if the untyped object doesn't represent a record (that is, if it represents a table or a scalar or primitive value).

Syntax

ColumnNames( UntypedRecord )

  • UntypedRecord – Required. An untyped object that represents a record.

Column( UntypedRecord, ColumnName )

  • UntypedRecord – Required. An untyped object that represents a record.
  • ColumnName - Required. The name of the column to be retrieved from the given record.

Examples

Accessing field values

Given the following JSON string in a variable named JsonString

{ "name": "Seattle", "population": 737000 }

  1. The following formula returns a single-column table with a Value column containing the following values: "name", "population":

    ColumnNames( ParseJSON( JsonString ) )

  2. The following formula returns the number 737000:

    Value( Column( ParseJSON( JsonString ), "population" ) )

    2.1. Notice that this is similar to the . operator for untyped records, but the column name doesn't have to be known beforehand.

  3. The following formula returns the text value "name: Seattle, population: 737000":

    With(
    { untyped: ParseJSON( JsonString ) },
    Concat(
        ColumnNames( untyped ),
        $"{Value}: {Column( untyped, Value )}",
        ", "))

Blanks

Given the following JSON string in a variable named JsonString

{ "text": "text value" , "number": 567, "empty": null }
  1. Attempting to access nonexisting fields returns Blank(). The following formula returns true: 
    IsBlank( Column( ParseJSON( JsonString ), "does not exist" ) )
  2. JSON null values are considered Blank(). The following formula returns true: 
    IsBlank( Column( ParseJSON( JsonString ), "empty" ) )

Nonrecords

Calling the Column or ColumnNames functions with untyped objects that don't represent records return an error. All of those expressions below are erroneous:

Formula Reason for error
ColumnNames( ParseJSON ( "[1, 2, 3]" ) ) Untyped object represents an array
Column( ParseJSON ( "23.45" ), "Value" ) Untyped object represents a number
ColumnNames( ParseJSON ( """hello""" ) ) Untyped object represents a text
Column( ParseJSON ( "{""a"":false}" ).a, "a" ) Untyped object represents a boolean value