Manipulating Nested Table with Function

[rpiboy_1]

Hi all,

Attempting to distill a larger problem into smaller bits.

Lets say I have a table that looks like this:

Index	DataType	ColumnOfTables
1	TypeA	<Table>
2	TypeB	<Table>
3	TypeA	<Table>
4	TypeC	<Table>

I have a function "FnTransformTable" that is intended to transform each table embedded in the column [ColumnOfTables]. The function accepts two arguements,

Tbl as table - the table to be transformed

Filter as text - value used as part of the transformation process

Now, I should be able to use Table.AddColumn, to add a new column [ColumnofTransformedTables] which contains the result of transforming each Table in [ColumnOfTables].

Result would look like this:

Index	DataType	ColumnOfTables	TransformedTables
1	TypeA	<Table>	<newTable>
2	TypeB	<Table>	<newTable>
3	TypeA	<Table>	<newTable>
4	TypeC	<Table>	<newTable>

However, when I try that basic expression, I get an error:

let
    Source = #"SomeQuery",
    #"Invoked Custom Function" = Table.AddColumn(Source, "FnIterator", each FnIterator([DetailedData], [Data Type Parameter]))

in
    #"Invoked Custom Function"

The error is: "Expression.Error: The field 'DetailedData' of the record wasn't found." Then provides a list of columns and field values from the embedded table for a single row. This suggest to me that is not properly 'getting' the embedded table.

[rpiboy_1 · Accepted solution]

So I finally solved my issue. After further reviewing (i.e. watching YouTube videos) that covered 'each' and '_' in more detail, to ensure I understood how that all worked, I discovered I was doing everything correctly. The problem was I didn't realize/forgot that in my function I had a drill down on the Field with the table, so in effect I was attempting to drill down twice, which explains the error. I disovered my mistake quite by accident after I was toying further with the code and making duplicates.

As they say, if you're trouble-shooting yourself, you're just workin'. 🙂

[ppm1]

In your example data, you should use [ColumnOfTables] as the table reference in your function. That is the field that hold the table on that row. Also, why is a custom function needed vs. a simple custom columne with an expression like

let

filtervalue = [DataType]

in

Table.SelectRows([ColumnOfTables], each [ColumnToFilter] = filtervalue)

What transformations is your function doing?

Pat

Pat