none
Get the parent row using PowerQuery RRS feed

  • Question

  • I have a dataset that has TaskId and ParentTaskId. For each row I would like to get some information from its parent but cannot seem to figure out exactly how to do it.  I've been trying something like what is show below but I get an error "Cannot convert a value of type Table to type Function"   Is there a better way of doing this (or is it even possible)?

    let
    Source = Excel.CurrentWorkbook(){[Name="Tasks"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [TaskId] = [ParentTaskId]),
    GABParentTaskName= FilteredRows([TaskName])
    in
    GABParentTaskName


    Gary A. Bushey

    Wednesday, December 31, 2014 1:51 PM

Answers

  • What you need to do is a join between the table and itself. Through the UI, you'd need to create the base table as a query and then you could use "merge" to join the table to itself. Doing the join by writing the code would produce something like this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tasks"]}[Content],
        Joined = Table.NestedJoin(Source,{"ParentTaskId"},Source,{"TaskId"},"Parent"),
        Expanded = Table.ExpandTableColumn(Joined, "Parent", {"TaskName"}, {"Parent.TaskName"})
    in
        Expanded

    Wednesday, December 31, 2014 3:30 PM