none
Dynamic Join in Power Query RRS feed

  • Question

  • Hi, 

    I have a big data table which needs to be filtered by several columns. I am thinking using inner join the filter table with data table to get results. The question is the filters are dynamic. For example, the user can use two columns to filter (Select data with Acct= 1001 or 1002 or 1003 or 1004 and Tran= 1 or 2 or 3). 

    col1 col2
    Acct Tran
    1001 1
    1002 2
    1003 3
    1004

    Or the user just add one column at the end of the table, using three columns to filter (Select data with Acct= 1001 or 1002 or 1003 or 1004 and Tran= 1 or 2 or 3 and Dept=a or b or c). 

    col1 col2 col3
    Acct Tran Dept
    1001 1 a
    1002 2 b
    1003 3 c
    1004

    I am not sure what column the user is going to put in the table and how many columns. Can anyone help me with that?

    Many thanks.

    Thursday, October 1, 2015 8:45 PM

Answers

  • You can use the following code, provided your data table is called "Data" & your Filter-table is called "Filter":

    let
        Data = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
        Source = Excel.CurrentWorkbook(){[Name="Filter"]}[Content],
        Column_Names = Table.ColumnNames(Source),
        AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
        Tables = Table.UnpivotOtherColumns(AddIndex, {"Index"}, "Attribute", "Value"),
        Group = Table.Group(Tables, {"Attribute"}, {{"Count", each _, type table}}),
        Trick = Table.AddColumn(Group, "Custom", each Table.NestedJoin([Count],{"Value"},Data,{_[Attribute]},"NewColumn",JoinKind.Inner)),
        RemoveOther = Table.SelectColumns(Trick,{"Custom"}),
        Expand1 = Table.ExpandTableColumn(RemoveOther, "Custom", {"Index", "Attribute", "Value", "NewColumn"}, {"Index", "Attribute", "Value", "NewColumn"}),
        RemoveOther2 = Table.SelectColumns(Expand1,{"NewColumn"}),
        Expand2 = Table.ExpandTableColumn(RemoveOther2, "NewColumn", Table.ColumnNames(Data), Table.ColumnNames(Data)),
        GroupFilter = Table.Group(Expand2, Column_Names, {{"Count", each _, type table}, {"Column", each Table.RowCount(_), type number}}),
        Remove = Table.RemoveColumns(GroupFilter,Column_Names),
        FilterMatches = Table.SelectRows(Remove, each ([Column] = List.Count(Group[Count]))),
        Remove2 = Table.RemoveColumns(FilterMatches,{"Column"}),
        ExpandCount = Table.ExpandTableColumn(Remove2, "Count", Table.ColumnNames(Data), Table.ColumnNames(Data)),
        RemoveDups = Table.Distinct(ExpandCount, Column_Names)
    in
        RemoveDups

    LinkToFile (Query: xlsSource)

    This solution is fully dynamic.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by bjzk Monday, October 5, 2015 3:16 PM
    Friday, October 2, 2015 2:47 PM
    Moderator

All replies

  • I think I just answered this same question on SO:

    http://stackoverflow.com/a/32898278/1787137

    Perhaps reply there if you need more help. 

    Friday, October 2, 2015 2:28 AM
  • Are your connecting to a SQL-server-source or Excel-table?

    Imke Feldmann TheBIccountant.com

    Friday, October 2, 2015 12:35 PM
    Moderator
  • I add comment there. Just hope we do not need to leave too many blank columns in front. Let the user decide how many columns that they want to use.

    Thanks

    Friday, October 2, 2015 2:37 PM
  • An excel table
    Friday, October 2, 2015 2:38 PM
  • You can use the following code, provided your data table is called "Data" & your Filter-table is called "Filter":

    let
        Data = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
        Source = Excel.CurrentWorkbook(){[Name="Filter"]}[Content],
        Column_Names = Table.ColumnNames(Source),
        AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
        Tables = Table.UnpivotOtherColumns(AddIndex, {"Index"}, "Attribute", "Value"),
        Group = Table.Group(Tables, {"Attribute"}, {{"Count", each _, type table}}),
        Trick = Table.AddColumn(Group, "Custom", each Table.NestedJoin([Count],{"Value"},Data,{_[Attribute]},"NewColumn",JoinKind.Inner)),
        RemoveOther = Table.SelectColumns(Trick,{"Custom"}),
        Expand1 = Table.ExpandTableColumn(RemoveOther, "Custom", {"Index", "Attribute", "Value", "NewColumn"}, {"Index", "Attribute", "Value", "NewColumn"}),
        RemoveOther2 = Table.SelectColumns(Expand1,{"NewColumn"}),
        Expand2 = Table.ExpandTableColumn(RemoveOther2, "NewColumn", Table.ColumnNames(Data), Table.ColumnNames(Data)),
        GroupFilter = Table.Group(Expand2, Column_Names, {{"Count", each _, type table}, {"Column", each Table.RowCount(_), type number}}),
        Remove = Table.RemoveColumns(GroupFilter,Column_Names),
        FilterMatches = Table.SelectRows(Remove, each ([Column] = List.Count(Group[Count]))),
        Remove2 = Table.RemoveColumns(FilterMatches,{"Column"}),
        ExpandCount = Table.ExpandTableColumn(Remove2, "Count", Table.ColumnNames(Data), Table.ColumnNames(Data)),
        RemoveDups = Table.Distinct(ExpandCount, Column_Names)
    in
        RemoveDups

    LinkToFile (Query: xlsSource)

    This solution is fully dynamic.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by bjzk Monday, October 5, 2015 3:16 PM
    Friday, October 2, 2015 2:47 PM
    Moderator
  • Hi Imke,

    Thanks for the help. It works. However, another question rises. Could you please help me with the below question?

    https://social.technet.microsoft.com/Forums/en-US/e4c31211-6cc7-44fb-ace5-b62f2c16f9ec/use-all-in-the-filter?forum=powerquery

    Many thanks.

    Monday, October 5, 2015 4:09 PM