none
Stuck at power query RRS feed

  • Question

  • I'm working on long and complicated power query and now I'm stuck at last step which is doing a combination of value's. I got it working with excel formula but my solution has only limit for 200values which is pretty low.This is the problem.

    (see letters as values)

    1. step (source)

    Column1 Column2

    a             b 

    c             d

    e             f

    c             f

    b             g

    2.Step (identify connected value's)

    Column1 Column2

    a            

    c             d

    e             f

    c             f

    b             g

    3.Step (connect value's)

    1.(a+b),(b+g)

    2.(c+d)+(c+f)+(e+f)

    4.Step (remove duplicates) /Final result

    1.a,b,g

    2.c,d,f,e


    Don't need any signs between the values it was only for understanding. But basically this need to find any connected value's. Result may have more one value which is connected to 30 or more values at once.

    Thank you for your help, very much appreciated


    • Edited by Mini849 Thursday, January 10, 2019 7:52 AM
    Thursday, January 10, 2019 7:51 AM

Answers

  • Hi,

    This code works with your example:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        group1 = Table.Group(Source, {"Column1"}, {{"temp", each _[Column2]}}),
        group2 = Table.Group(Source, {"Column2"}, {{"temp", each _[Column1]}}),
        combine = Table.Combine({group1,group2}),
        merged = Table.CombineColumns(combine,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"letter"),
        sorted = Table.Sort(merged,{{"letter", Order.Ascending}}),
        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]})))),
        list4 = Table.AddColumn(list3, "list4", (z) => List.Sort(List.Distinct(List.Combine(Table.SelectRows(list3, each List.Contains([list3],z[letter]))[list3])))),
        combinations = Table.AddColumn(list4, "combinations", each Text.Combine([list4], ", ")),
        group = Table.Group(combinations, {"combinations"}, {})
    in
        group

    But keep in mind, that for more connected levels (for instance, e-->x and x -->y) you should add further steps on the same technique or use another method (recursive function, perhaps).

    • Edited by Aleksei Zhigulin Thursday, January 10, 2019 11:19 AM
    • Marked as answer by Mini849 Thursday, January 10, 2019 12:23 PM
    • Unmarked as answer by Mini849 Thursday, January 10, 2019 12:39 PM
    • Marked as answer by Mini849 Thursday, January 10, 2019 12:39 PM
    Thursday, January 10, 2019 11:14 AM

All replies

  • Hi,

    This code works with your example:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        group1 = Table.Group(Source, {"Column1"}, {{"temp", each _[Column2]}}),
        group2 = Table.Group(Source, {"Column2"}, {{"temp", each _[Column1]}}),
        combine = Table.Combine({group1,group2}),
        merged = Table.CombineColumns(combine,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"letter"),
        sorted = Table.Sort(merged,{{"letter", Order.Ascending}}),
        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]})))),
        list4 = Table.AddColumn(list3, "list4", (z) => List.Sort(List.Distinct(List.Combine(Table.SelectRows(list3, each List.Contains([list3],z[letter]))[list3])))),
        combinations = Table.AddColumn(list4, "combinations", each Text.Combine([list4], ", ")),
        group = Table.Group(combinations, {"combinations"}, {})
    in
        group

    But keep in mind, that for more connected levels (for instance, e-->x and x -->y) you should add further steps on the same technique or use another method (recursive function, perhaps).

    • Edited by Aleksei Zhigulin Thursday, January 10, 2019 11:19 AM
    • Marked as answer by Mini849 Thursday, January 10, 2019 12:23 PM
    • Unmarked as answer by Mini849 Thursday, January 10, 2019 12:39 PM
    • Marked as answer by Mini849 Thursday, January 10, 2019 12:39 PM
    Thursday, January 10, 2019 11:14 AM
  • Hello I think I understand the way how its should work, but I dont know why but once I tried  to group it to group1, the query start to load for ever. Maybe I try it in my personal laptop later. For now thanks, a lot :).
    Thursday, January 10, 2019 12:23 PM