Forum Discussion

willtunechi's avatar
willtunechi
Frequent Visitor
2 years ago
Solved

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

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

 

 

  • Change

    each Record.Field(_,Parent)=null

    to 

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

3 Replies