Power Query Table.TransformRows won't return table

3
votes

In the M language documentation for Table.TransformRows, it lists the type signature as Table.TransformRows(table as table, transform as function) as list followed by the slightly cryptic comment

If the return type of the transform function is specified, then the result will be a table with that row type.

However, whenever I define my function, Power Query always returns me a list instead of a table. For instance, this query:

    func = (row) as record => [B = Number.ToText(row[a])] as record,
    #"firstTable"= Table.FromRecords({
            [a = 1],
            [a = 2],
            [a = 3],
            [a = 4],
            [a = 5]}),
    #"myTable" = Table.TransformRows(#"firstTable",func)
in
  #"myTable"

returns a table. I had wondered if I needed to create a record type which specifies the type of each record entry, but if I try

myType = type [B = text],
func = (row) as record => [B = Number.ToText(row[a])] as myType

then it tells me that my type identifier is invalid on the second line. Finally, I tried to set the function return type using

myType = type [B = text],
func1 = (row) as record => [B = Number.ToText(row[a])] as myType,
funcType = Type.ForFunction([ReturnType = myType, Parameters = [X = type number]], 1),
func = Value.ReplaceType(func1,funcType)

but Table.TransformRows still returns a list.

I am aware that I am able to use Table.FromRecords or Table.FromRows to turn the result of Table.TransformRows into a table, but I am currently experiencing some performance issues with these functions and was trying to cut them out to see if this would fix those issues

1
powerbipowerquerym

1 Answers

0
votes

typing inline

returns a table. I had wondered if I needed to create a record type which specifies the type of each record entry

// you had this
record_list = { [ a = 1 ], [ a = 2 ], [ a = 3 ], [ a = 4 ], [ a = 5 ] },
firstTable = Table.FromRecords(
    record_list
)

Which ends up as a column of type any. To fix that:

// declare the type
firstTable = Table.FromRecords(
    record_list,
    type table[a = text]
)

Whenever a function takes the parameter columns as any you can use any of these formats

  • "Name"
  • `{ "Name1", "Name2" }
  • type table[ Name1 = text, Name2 = number ]

Invalid type Identifier

then it tells me that my type identifier is invalid on the second line

The as operator only works on primitive data types.

If it were to work, this would mean assert the record is a myType, then the function return asserts the type is record, which is mixed types. The function already has a final assert, so you don't need one on your inner calls.

func = (row) as record => [B = Number.ToText(row[a])] as something

Replacing Function Types does not change behavior

Finally, I tried to set the function return type using ... Value.ReplaceType

Using Value.ReplaceType on a function does not modify the actual types or behavior. That's just changing the metadata.

Ben Gribaudo: /part18-Custom-Type-Systems As type ascription can only change information about a function, not the function’s behavior at the language level, ascribing a type onto a function that specifies different argument or return assertions has no effect on the behavior of the function. The mashup engine always uses the type assertions specified when the function was originally defined, even if another type is later ascribed onto it.

Check out Ben's Power Query series. It's high quality. It goes into far more detail than other places.

Performance

I am aware that I am able to use Table.FromRecords or Table.FromRows to turn the result of Table.TransformRows into a table, but I am currently experiencing some performance issues

Missing column data-types can cause issues. But you really should try the query diagnostics, it will tell you what steps are taking the most time.

Based on your example, you might want transform column instead of rows.

Also make sure your query is folding!

enter image description here

Example using Table.TransformRows with typing

let   
    // sample data, list of records like: [ Number = 0 ]
    numberRecords = List.Transform( {0..10}, each [Number = _] ),
    Source = Table.FromRecords(
        numberRecords,
        type table[Number = number]
    ),

    transform_SquareNumbers = (source as table) => 
        let 
            rows = Table.TransformRows(
                source, (row) as record =>
                    [
                        Number = Number.Power(
                            row[Number], 2
                        )
                    ]
            ),
            result = Table.FromRecords(rows),
            source_type = Value.Type(source)
        in
            Value.ReplaceType(result, source_type),   
    
    FinalTable = transform_SquareNumbers(Source)
in
    FinalTable

enter image description here