none
Identify Parent column information in a tree structure in Power query

    Question

  • Hi,

    Need help how to retrieve column "Origin" below in power query from "Pay" and "Take" columns:

    PAY TAKE ORIGIN
    A B A
    B C A
    C D C
    P Q P
    U V U
    V W U

    Help is well appreciated.

    Thanks you.

    Sunday, May 19, 2019 1:58 PM

Answers

  • Hi,

    This problem is similar to following thread:

    https://social.technet.microsoft.com/Forums/en-US/46e09cf4-f8c2-4829-9b38-9c1f83c4b7ce/stuck-at-power-query?forum=powerquery

    After slight edits the code should work with your data sample:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        group1 = Table.Group(Source, {"PAY"}, {{"temp", each [TAKE]}}),
        group2 = Table.Group(Source, {"TAKE"}, {{"temp", each [PAY]}}),
        combine = Table.Combine({group1,group2}),
        merged = Table.CombineColumns(combine,{"PAY", "TAKE"},Combiner.CombineTextByDelimiter("", 1),"letter"),
        sorted = Table.Sort(merged,{{"letter", 0}}),
        list1 = Table.Group(sorted, {"letter"}, {{"list1", each List.Combine([temp])}}),
        list2 = Table.AddColumn(list1, "list2", (z) => List.Combine(Table.SelectRows(list1, each List.Contains([list1],z[letter]))[list1])),
        list3 = Table.AddColumn(list2, "list3", each List.Sort(List.Distinct(List.Combine({[list1],[list2]})))),
        origin = Table.AddColumn(list3, "ORIGIN", (z) => List.First(List.Sort(List.Distinct(List.Combine(Table.SelectRows(list3, each List.Contains([list3],z[letter]))[list3]))))),
        merged1 = Table.NestedJoin(Source,{"PAY"},origin,{"letter"},"temp",JoinKind.LeftOuter),
        expand = Table.ExpandTableColumn(merged1, "temp", {"ORIGIN"})
    in
        expand


    Sunday, May 19, 2019 5:42 PM

All replies

  • Hi,

    This problem is similar to following thread:

    https://social.technet.microsoft.com/Forums/en-US/46e09cf4-f8c2-4829-9b38-9c1f83c4b7ce/stuck-at-power-query?forum=powerquery

    After slight edits the code should work with your data sample:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        group1 = Table.Group(Source, {"PAY"}, {{"temp", each [TAKE]}}),
        group2 = Table.Group(Source, {"TAKE"}, {{"temp", each [PAY]}}),
        combine = Table.Combine({group1,group2}),
        merged = Table.CombineColumns(combine,{"PAY", "TAKE"},Combiner.CombineTextByDelimiter("", 1),"letter"),
        sorted = Table.Sort(merged,{{"letter", 0}}),
        list1 = Table.Group(sorted, {"letter"}, {{"list1", each List.Combine([temp])}}),
        list2 = Table.AddColumn(list1, "list2", (z) => List.Combine(Table.SelectRows(list1, each List.Contains([list1],z[letter]))[list1])),
        list3 = Table.AddColumn(list2, "list3", each List.Sort(List.Distinct(List.Combine({[list1],[list2]})))),
        origin = Table.AddColumn(list3, "ORIGIN", (z) => List.First(List.Sort(List.Distinct(List.Combine(Table.SelectRows(list3, each List.Contains([list3],z[letter]))[list3]))))),
        merged1 = Table.NestedJoin(Source,{"PAY"},origin,{"letter"},"temp",JoinKind.LeftOuter),
        expand = Table.ExpandTableColumn(merged1, "temp", {"ORIGIN"})
    in
        expand


    Sunday, May 19, 2019 5:42 PM
  • Thank you so much.

    For my sample data, it works as desired. I will apply with real dataset and shall revert back if there are any challenges.

    Monday, May 20, 2019 7:11 AM