Error We cannot convert the value "Parent1" to type Function.

[willtunechi]

hello, 

I'm trying to run the code below from a function, but I'm getting the error An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "Parent1" to type Function.

Does anybody know how to solve this ?

table is

the code is

(Table as table, Parent as text, Child as text, Qty as text) =>
let
    ChgTypeKeyCols = Table.Buffer(Table.TransformColumnTypes(Table,{{Parent, type text},{Child, type text}})),
    SelectTopParents = Table.SelectRows(ChgTypeKeyCols, each Record.Field(_,Parent)=null),

    AddPath = Table.AddColumn(SelectTopParents, "Path", each #table ({"Path_", "Qty_"}, {{Record.Field(_, Child), Record.Field(_, Qty)}})),

    ResolveBOM = List.Generate(()=>
    [Result=AddPath, Level=0],
    each Table.RowCount([Result]) > 0,
    each [ Result = let
                A = Table.NestedJoin(ChgTypeKeyCols, {Parent}, [Result], {Child}, "NewColumn", JoinKind.Inner),
                B = Table.ExpandTableColumn(A, "NewColumn", {"Path"},{"PathOld"}),
                C = Table.AddColumn(B, "Path", each Table.Combine({[PathOld], #table({"Path_", "Qty_"}, {{Record.Field(_,Child), Record.Field(_,Qty)}})}))
                in C,
                Level = [Level]+1]),
                ConvertToTable = Table.FromList(ResolveBOM, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                ExpandBOM1 = Table.ExpandRecordColumn(ConvertToTable, Table.ColumnNames(Table){0}("Column1"), {"Level", "Result"}, {"Level", "Result"}),
                ExpandBOM2 = Table.ExpandTableColumn(ExpandBOM1,"Result", Table.ColumnNames(ExpandBOM1[Result]{1})),
                
                AddFields = Table.AddColumn(ExpandBOM2, "NewFields", each [
                    TotalQty = List.Product([Path][Qty_]),
                    SpacedPath = Text.Repeat("    | ", [Level])&Record.Field(_, Child),
                    PathItems = [Path][Path_],
                    PathExplode = Text.Combine(PathItems, "/"),
                    TopParentProduct = PathItems{0} ]),
                ExpandNewFields = Table.ExpandRecordColumn(AddFields, "NewFields", Record.FieldNames(AddFields[NewFields]{0})),

                PurchaseItems = Table.Buffer(Table.FromColumns({List.Difference(List.Distinct(Table.Column(ChgTypeKeyCols, Child)), List.Distinct(Table.Column(ChgTypeKeyCols, Parent)))})),
                MergePurchaseItem = Table.NestedJoin(ExpandNewFields, {Child}, PurchaseItems, Table.ColumnNames(Table){0}({"Column1"}), "NewColumn", JoinKind.LeftOuter),
                ExpandPurchaseItem = Table.ExpandTableColumn(MergePurchaseItem, "NewColumn", Table.ColumnNames(Table){0}({"Column1"}), {"PurchaseItem"}),
                Cleanup1 = Table.RemoveColumns(ExpandPurchaseItem, {"PathOld", "Path","PathItems"}),
                Cleanup2 = Table.TransformColumnTypes(Cleanup1,{{Qty, type number}, {"TotalQty", type number}})

            in
                Cleanup2

[lbendlin · Accepted solution]

Change

each Record.Field(_,Parent)=null

to 

each Record.Field(_,"Parent")=null

[willtunechi]

thank you @lbendlin 

After changing as per your recommendation, the following error message appears

An error occurred in the ‘’ query. Expression.Error: The field 'Parent' of the record wasn't found.
Details:
Parent1=A1
Child1=B1
Qty=1

and if I change the column name "Parent1" to "Parent", the error is:

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "Parent" to type Function.
Details:
Value=Parent
Type=[Type]

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...