March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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'. 🙂
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'. 🙂
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.