Forum Discussion
Anonymous
2 years agoNot applicable
An error occurred in the ‘Query2’ query. Expression.Error: There is an unknown identifier.
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
2 Replies
- amitchandak
Super User
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})
- AnonymousNot applicable
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!