An error occurred in the ‘Query2’ query. Expression.Error: There is an unknown identifier.

[Anonymous]

Hi community. I am getting the an error in my Invoked Function. This is the code i used. Thank you!

= let 
    funcWithPathItem = (ParChTable as table, ChildKey as text, ParentKey as text, LevelColumnName as text) =>
        let
            SelectRelevantColumns = Table.SelectColumns(ParChTable, {ChildKey, ParentKey, LevelColumnName}),
            #"Changed Type" = Table.TransformColumnTypes(SelectRelevantColumns, {{ChildKey, type text}, {ParentKey, type text}}),
            ReplaceNulls = Table.ReplaceValue(#"Changed Type", null, "", Replacer.ReplaceValue, {ParentKey}),
            MissingParents = List.Buffer(List.Select(List.Difference(List.Distinct(Table.Column(ReplaceNulls, ParentKey)), List.Distinct(Table.Column(ReplaceNulls, ChildKey))), each _ <> "")),
            AddMissingParents = Table.Buffer(Table.Combine({ReplaceNulls, #table({ChildKey, LevelColumnName, ParentKey}, List.Transform(MissingParents, each {_, "Unknown TopLevel" & Text.From(List.PositionOf(MissingParents, _)), ""}))})),
            #"Merged Queries0" = Table.NestedJoin(AddMissingParents, {ChildKey}, AddMissingParents, {ParentKey}, "SelectRelevantColumns", JoinKind.LeftOuter),
            CheckIfIsLeaf = Table.AddColumn(#"Merged Queries0", "IsLeaf", each if Table.IsEmpty([SelectRelevantColumns]) then "yes" else "no"),
            #"Replaced Value1" = Table.ReplaceValue(CheckIfIsLeaf, null, "", Replacer.ReplaceValue, {ParentKey, LevelColumnName}),
            AddStartPath = Table.AddColumn(#"Replaced Value1", "Path", each Text.Trim(Record.Field(_, ChildKey) & "|" & Record.Field(_, ParentKey), "|")),
            #"Duplicated Column" = Table.DuplicateColumn(AddStartPath, LevelColumnName, "FirstName"),
            Feed = Table.DuplicateColumn(#"Duplicated Column", ParentKey, "FirstParentKey"),
            
            // Split hierarchy into levels using PATHITEM()
            SplitIntoLevels = Table.AddColumn(Feed, "LevelPath", 
                List.Transform(
                    Text.Split([Path], "|"), 
                    (x) => if x = "" then null else x
                )
            )
        in
            SplitIntoLevels
in
    funcWithPathItem

[amitchandak]

@Anonymous , What is the objective of (x) => if x = "" then null else x, will only Text.Split will not help ?

SplitIntoLevels = Table.TransformColumns(DuplicatedColumn, {"LevelPath", each Text.Split(_, "|"), type list})

[Anonymous]

Hi @amitchandak Thank you for your reply. I managed to invoke the function. However, it is not working as intended... what i wan to achieve is something like the PATHITEM function where the path is split into its specific level. Do you know what i have to change? Thank You!