Manipulating Nested Table with Function

[Syndicate_Admin]

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	2 TypeB 3 TypeA 4 TypeC 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 2 TypeB 3 TypeA 4 TypeC 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.

[Syndicate_Admin]

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

[Syndicate_Admin]

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'. 🙂