Filter Values Based on Table

Generate Result table on the basis of T1 and T2. If a given data in T1, doesn’t appear in T2, then this need to be omitted from the Result table. Ex. Psychology

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 61
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Filter Values Based on Table with Power Query

Power Query solution 1 for Filter Values Based on Table, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T2 = Table.Buffer(Excel.CurrentWorkbook(){[Name = "Table2"]}[Content]), 
  Join = Table.AddColumn(Source, "Cat", each T2{[Data = [Data]]}?[Category]?), 
  Pivoted = Table.Pivot(
    Join, 
    List.Distinct(T2[Category]), 
    "Cat", 
    "Data", 
    each Text.Combine(_, ", ")
  )
in
  Pivoted

Power Query solution 2 for Filter Values Based on Table, proposed by Zoran Milokanović:

let
  // Table T1 
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  // Table T2 
  Source2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  AddedCategory = Table.AddColumn(Source, "Category", each Source2{[Data = [Data]]}?[Category]?), 
  FilteredNotNulls = Table.SelectRows(AddedCategory, each ([Category] <> null)), 
  GroupedCategoryAndID = Table.Group(
    FilteredNotNulls, 
    {"ID", "Category"}, 
    {{"Data", each Text.Combine(_[Data], ", ")}}
  ), 
  PivotedCategory = Table.Pivot(
    GroupedCategoryAndID, 
    List.Distinct(GroupedCategoryAndID[Category]), 
    "Category", 
    "Data"
  )
in
  PivotedCategory

Power Query solution 3 for Filter Values Based on Table, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(), 
  #"Expand" = Table.ExpandTableColumn(
    Source, 
    "Content", 
    {"ID", "Data", "Category"}, 
    {"Content.ID", "Content.Data", "Content.Category"}
  ), 
  #"Renamed" = Table.RenameColumns(#"Expand", {{"Content.ID", "ID"}}), 
  #"Merged" = Table.NestedJoin(
    #"Renamed", 
    {"Content.Data"}, 
    #"Renamed", 
    {"Content.Data"}, 
    "Expanded Content", 
    JoinKind.LeftOuter
  ), 
  #"Expanded" = Table.ExpandTableColumn(
    #"Merged", 
    "Expanded Content", 
    {"ID", "Content.Data", "Content.Category", "Name"}, 
    {
      "Expanded Content.Content.ID", 
      "Expanded Content.Content.Data", 
      "Expanded Content.Content.Category", 
      "Expanded Content.Name"
    }
  ), 
  #"Filter" = Table.SelectRows(
    #"Expanded", 
    each ([Expanded Content.Content.Category] <> null) and ([ID] <> null)
  ), 
  #"Removed" = Table.RemoveColumns(
    #"Filter", 
    {
      "Content.Category", 
      "Name", 
      "Expanded Content.Content.ID", 
      "Expanded Content.Name", 
      "Expanded Content.Content.Data"
    }
  ), 
  #"Grouped" = Table.Group(
    #"Removed", 
    {"ID", "Expanded Content.Content.Category"}, 
    {{"Group", each Text.Combine([Content.Data], ", "), type text}}
  ), 
  #"Pivoted" = Table.Pivot(
    #"Grouped", 
    List.Distinct(#"Grouped"[#"Expanded Content.Content.Category"]), 
    "Expanded Content.Content.Category", 
    "Group"
  )
in
  #"Pivoted"

Power Query solution 4 for Filter Values Based on Table, proposed by Rick de Groot:

let
  Source = Table1, 
  DataType = Table.TransformColumnTypes(Source, {{"ID", Int64.Type}, {"Data", type text}}), 
  CombineTables = Table.NestedJoin(
    DataType, 
    {"Data"}, 
    Table2, 
    {"Data"}, 
    "Table2", 
    JoinKind.LeftOuter
  ), 
  ExpFields = Table.ExpandTableColumn(CombineTables, "Table2", {"Category"}, {"Category"}), 
  RemoveNull = Table.SelectRows(ExpFields, each [Category] <> null), 
  PivotCol = Table.Pivot(
    RemoveNull, 
    List.Distinct(RemoveNull[Category]), 
    "Category", 
    "Data", 
    each Text.Combine(_, ", ")
  )
in
  PivotCol

Power Query solution 5 for Filter Values Based on Table, proposed by Aditya Kumar Darak 🇮🇳:

let
  T1 = Excel.CurrentWorkbook(){[Name = "_T1"]}[Content], 
  T2 = Excel.CurrentWorkbook(){[Name = "_T2"]}[Content], 
  Category = Table.AddColumn(T1, "Category", each T2{[Data = [Data]]}?[Category]?), 
  Filter = Table.SelectRows(Category, each [Category] <> null), 
  Return = Table.Pivot(
    Filter, 
    List.Distinct(Filter[Category]), 
    "Category", 
    "Data", 
    each Text.Combine(_, ", ")
  )
in
  Return

Power Query solution 6 for Filter Values Based on Table, proposed by Aditya Kumar Darak 🇮🇳:

let
  T1       = Excel.CurrentWorkbook(){[Name = "_T1"]}[Content], 
  T2       = Excel.CurrentWorkbook(){[Name = "_T2"]}[Content], 
  Category = Table.AddColumn(T1, "Category", each T2{[Data = [Data]]}?[Category]?), 
  Filter   = Table.SelectRows(Category, each [Category] <> null), 
  Group    = Table.Group(Filter, {"ID", "Category"}, {"Combine", each Text.Combine([Data], ", ")}), 
  Return   = Table.Pivot(Group, List.Distinct(Group[Category]), "Category", "Combine")
in
  Return

Power Query solution 7 for Filter Values Based on Table, proposed by Aditya Kumar Darak 🇮🇳:

let
  T1 = Excel.CurrentWorkbook(){[Name = "_T1"]}[Content], 
  T2 = Excel.CurrentWorkbook(){[Name = "_T2"]}[Content], 
  Join = Table.NestedJoin(T1, "Data", T2, "Data", "Join", JoinKind.Inner), 
  Expand = Table.ExpandTableColumn(Join, "Join", {"Category"}), 
  Return = Table.Pivot(
    Expand, 
    List.Distinct(Expand[Category]), 
    "Category", 
    "Data", 
    each Text.Combine(_, ", ")
  )
in
  Return

Power Query solution 8 for Filter Values Based on Table, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Lookup = Table.AddColumn(Source, "Custom", each try Table2{[Data=[Data]]}[Category] otherwise null),
 NoNulls = Table.SelectRows(Lookup, each ([Custom] <> null)),
 Group = Table.Group(NoNulls, {"ID", "Custom"}, {{"Count", each Text.Combine([Data], ", ")}}),
 Sol = Table.Pivot(Group, List.Distinct(Group[Custom]), "Custom", "Count")
in
 Sol
Aplicando Excel BI's video:
https://www.linkedin.com/feed/update/urn:li:activity:7017446873569374208/

Power Query solution 9 for Filter Values Based on Table, proposed by Luan Rodrigues:

let
  Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content], 
  Fonte2 = Excel.CurrentWorkbook(){[Name = "Tabela2"]}[Content], 
  mesc = Table.NestedJoin(Fonte2, {"Data"}, Fonte, {"Data"}, "Personalizar1", JoinKind.LeftOuter)[
    [Category], 
    [Personalizar1]
  ], 
  exp1 = Table.ExpandTableColumn(mesc, "Personalizar1", Table.ColumnNames(mesc[Personalizar1]{0})), 
  fil = Table.SelectRows(exp1, each ([ID] <> null)), 
  gp = Table.Group(
    fil, 
    {"ID"}, 
    {{"Contagem", each Table.Group(_, {"Category"}, {{"Count", each Text.Combine(_[Data], ", ")}})}}
  ), 
  exp = Table.ExpandTableColumn(gp, "Contagem", {"Category", "Count"}), 
  res = Table.Pivot(exp, List.Distinct(exp[Category]), "Category", "Count")
in
  res

Power Query solution 10 for Filter Values Based on Table, proposed by Brian Julius:

let
  Source = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]), 
  Table1 = Table.SelectRows(
    Table.RemoveColumns(Source, {"Column3", "Data_1", "Category"}), 
    each [ID] <> null
  ), 
  Table2 = Table.SelectColumns(Source, {"Data_1", "Category"}), 
  Join = Table.RemoveColumns(Table.Join(Table1, "Data", Table2, "Data_1"), "Data_1"), 
  Group = Table.Group(Join, {"ID", "Category"}, {{"All", each [Data]}}), 
  Extract = Table.TransformColumns(
    Group, 
    {"All", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
  ), 
  Pivot = Table.Pivot(Extract, List.Distinct(Extract[Category]), "Category", "All")
in
  Pivot

Power Query solution 11 for Filter Values Based on Table, proposed by Eric Laforce:

let
  Source1 = Excel.CurrentWorkbook(){[Name = "tData61_1"]}[Content], 
  Source2 = Excel.CurrentWorkbook(){[Name = "tData61_2"]}[Content], 
  Join = Table.Join(Source1, "Data", Source2, "Data"), 
  Group = Table.Group(
    Join, 
    {"ID", "Category"}, 
    {{"Data", each Text.Combine(List.Sort(_[Data]), ", ")}}
  ), 
  Pivot = Table.Pivot(Group, List.Distinct(Group[Category]), "Category", "Data")
in
  Pivot

Power Query solution 12 for Filter Values Based on Table, proposed by Jaroslaw Kujawa:

let
  T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Source = Table.NestedJoin(T1{"Data"}, T2, {"Data"}, "Table2", JoinKind.Inner), 
  Expanded = Table.ExpandTableColumn(Source, "Table2", {"Category"}, {"Table2.Category"}), 
  #"Reordered Columns" = Table.ReorderColumns(Expanded, {"ID", "Table2.Category", "Data"}), 
  #"Grouped Rows" = Table.Group(
    #"Reordered Columns", 
    {"ID", "Table2.Category"}, 
    {{"Count", each _, type table [ID = number, Table2.Category = text, Data = text]}}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows", 
    "Custom", 
    each [
      a = Lines.ToText(Table.ToList(Table.SelectColumns([Count], {"Data"})), ", "), 
      b = Text.Start(a, Text.Length(a) - 2)
    ][b]
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Count"}), 
  #"Pivoted Column" = Table.Pivot(
    #"Removed Columns", 
    List.Distinct(#"Removed Columns"[Table2.Category]), 
    "Table2.Category", 
    "Custom"
  )
in
  #"Pivoted Column"

Power Query solution 13 for Filter Values Based on Table, proposed by Victor Wang:

let
  Source = Table.Join(T1, "Data", T2, "Data"), 
  theFx = (tbl as table, crit as text) as text =>
    Text.Combine(Table.SelectRows(tbl, (a) => a[Category] = crit)[Data], ", "), 
  Group = Table.Group(
    Source, 
    {"ID"}, 
    {
      {
        "all", 
        each [Planet = theFx(_, "Planet"), River = theFx(_, "River"), Subject = theFx(_, "Subject")]
      }
    }
  ), 
  Expand = Table.ExpandRecordColumn(Group, "all", {"Planet", "River", "Subject"}), 
  Sort = Table.Sort(Expand, {{"ID", 0}})
in
  Sort

Power Query solution 14 for Filter Values Based on Table, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  #"Merged Queries" = Table.NestedJoin(
    Source, 
    {"Data"}, 
    Custom1, 
    {"Data"}, 
    "Table2", 
    JoinKind.Inner
  ), 
  #"Expanded Table2" = Table.ExpandTableColumn(
    #"Merged Queries", 
    "Table2", 
    {"Data", "Category"}, 
    {"Data.1", "Category"}
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2", {"Data.1"}), 
  #"Pivoted Column" = Table.Pivot(
    #"Removed Columns", 
    List.Distinct(#"Removed Columns"[Category]), 
    "Category", 
    "Data", 
    each Text.Combine(_, ",")
  )
in
  #"Pivoted Column"

Power Query solution 15 for Filter Values Based on Table, proposed by Udit Chatterjee:

let
 Source = pqChallenge61A,
 LookUpTable = pqChallenge61B,
 lookupCategory = Table.AddColumn(
 Source, "Category", each try LookUpTable{[Data = [Data]]}[Category] otherwise null, type text
 ),
 concatenationGrouping = Table.Group(
 lookupCategory, {"ID", "Category"}, {{"Count", each Text.Combine([Data], ", "), type text}}
 ),
 filterNullCategories = Table.SelectRows(concatenationGrouping, each ([Category] <> null)),
 pivotData = Table.Pivot(filterNullCategories, List.Distinct(filterNullCategories[Category]), "Category", "Count")
in
 pivotData
Thanks to Alejandro Simón for pointing to the ExcelBI's  video on alternatives of merging. This is something new to me. Do you have any youtube channel Excel BI 🤔 I would like to see and learn more PowerQuery stuffs... 🧙‍♂️

Power Query solution 16 for Filter Values Based on Table, proposed by Gráinne Duggan:

let
  SourceT1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T1 = Table.TransformColumnTypes(SourceT1, {{"ID", type text}, {"Data", type text}}), 
  SourceT2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  T2 = Table.TransformColumnTypes(SourceT2, {{"Data", type text}, {"Category", type text}}), 
  Source = Table.NestedJoin(T1, {"Data"}, T2, {"Data"}, "Table2", JoinKind.Inner), 
  ExpandMergedtbls = Table.ExpandTableColumn(Source, "Table2", {"Category"}, {"Category"}), 
  GroupedIDCategory = Table.Group(
    ExpandMergedtbls, 
    {"ID", "Category"}, 
    {{"Count", each Text.Combine([Data], ", "), type nullable text}}
  ), 
  PivotedCategory = Table.Pivot(
    GroupedIDCategory, 
    List.Distinct(GroupedIDCategory[Category]), 
    "Category", 
    "Count"
  )
in
  PivotedCategory

Power Query solution 17 for Filter Values Based on Table, proposed by kamal shaterian:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"ID", Int64.Type}, {"Data", type text}}), 
  #"Merged Queries" = Table.NestedJoin(
    #"Changed Type", 
    {"Data"}, 
    Table2, 
    {"Data"}, 
    "Table2", 
    JoinKind.Inner
  ), 
  #"Expanded Table2" = Table.ExpandTableColumn(
    #"Merged Queries", 
    "Table2", 
    {"Category"}, 
    {"Category"}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Expanded Table2", 
    {"ID", "Category"}, 
    {{"Merged", each Text.Combine([Data], ","), type nullable text}}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Grouped Rows", 
    List.Distinct(#"Grouped Rows"[Category]), 
    "Category", 
    "Merged"
  )
in
  #"Pivoted Column"

Solving the challenge of Filter Values Based on Table with Excel

Excel solution 1 for Filter Values Based on Table, proposed by Bo Rydobon 🇹🇭:

=LET(a,A2:A16,b,B2:B16,d,D2:D26,e,E2:E26,h,TOROW(UNIQUE(e)),i,UNIQUE(a),HSTACK(VSTACK(A1,i),VSTACK(h,MAP(IFNA(i,h),IFNA(h,i),LAMBDA(j,k,TEXTJOIN(", ",,REPT(b,(a=j)*(XLOOKUP(b,d,e,0)=k))))))))

Excel solution 2 for Filter Values Based on Table, proposed by 🇰🇷 Taeyong Shin:

=LET(d,B2:B16,PIVOTBY(A2:A16,VLOOKUP(d,D2:E26,2,),d,ARRAYTOTEXT,,0,,0,,COUNTIF(D2:D26,d)))

=LET(i,A2:A16,d,B2:B16,a,E2:E26,MAKEARRAY(MAX(i)+1,ROWS(UNIQUE(a))+1,LAMBDA(r,c,IFS(r=1,INDEX(VSTACK(A1,a),c,1),c=1,r-1,1,TEXTJOIN(", ",,REPT(d,(i=r-1)*(XLOOKUP(d,D2:D26,a,0)=INDEX(UNIQUE(a),c-1,1))))))))

Excel solution 3 for Filter Values Based on Table, proposed by Kris Jaganah:

=LET(a,A2:A16,b,B2:B16,c,D2:D26,d,E2:E26,e,XLOOKUP(b,c,d,""),f,FILTER(HSTACK(a,e,b,a&e),e<>""),g,UNIQUE(TAKE(f,,-1)),h,MAP(g,LAMBDA(x,ARRAYTOTEXT(FILTER(CHOOSECOLS(f,3),TAKE(f,,-1)=x)))),i,UNIQUE(a),j,TOROW(UNIQUE(CHOOSECOLS(f,2))),VSTACK(HSTACK("ID",j),HSTACK(i,XLOOKUP(i&j,g,h,""))))

_x000D_

Excel solution 4 for Filter Values Based on Table, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _t1, A2:B16,
 _t2, D2:E26,
 _id, TAKE(_t1, , 1),
 _d, TAKE(_t1, , -1),
 _ct, IFNA(VLOOKUP(_d, _t2, 2, 0), ""),
 _fct, UNIQUE(FILTER(_ct, _ct <> "")),
 _fid, UNIQUE(_id),
 _e, LAMBDA(r, c,
 ARRAYTOTEXT(
 FILTER(
 _d,
 (_id = INDEX(_fid, r)) * (_ct = INDEX(_fct, c)),
 ""
 )
 )
 ),
 _fd, MAKEARRAY(ROWS(_fid), ROWS(_fct), _e),
 _r, VSTACK(HSTACK("Id", TOROW(_fct)), HSTACK(_fid, _fd)),
 _r
)

_x000D_

_x000D_

Excel solution 5 for Filter Values Based on Table, proposed by Hussein SATOUR:

=LET(I, A2:A16, di, B2:B16, ci, XLOOKUP(di, D2:D26, F2:F26,""), ui, UNIQUE(I), uci, UNIQUE(ci), z, MAP(ui&TOROW(uci), LAMBDA(x, ARRAYTOTEXT(FILTER(di, I&ci = x,"")))),
HSTACK(VSTACK("ID", ui), DROP(VSTACK(TOROW(uci),z),,-1)))

_x000D_

_x000D_

Excel solution 6 for Filter Values Based on Table, proposed by Oscar Mendez Roca Farell:

=LET(_a;A2:A16;_b;B2:B16;_d;D2:D26;_e;E2:E26;
_m;IFNA(HSTACK(_a;_b;XLOOKUP(_b;_d;_e));"");
 _v;TOROW(UNIQUE(_e))&UNIQUE(_a);_w;ROWS(_v);
 _r;WRAPROWS(IFERROR(SCAN("";SEQUENCE(COUNTA(_v));
 LAMBDA(i;x;TEXTJOIN(", ";;FILTER(INDEX(_m;;2);INDEX(_m;;3)&INDEX(_m;;1)=
 INDEX(_v;INT((x-1)/_w)+1;MOD(x-1;_w)+1)))));"");
 _w);VSTACK(HSTACK("ID";TOROW(UNIQUE(_e)));HSTACK(SEQUENCE(_w);_r)))

_x000D_

_x000D_

Excel solution 7 for Filter Values Based on Table, proposed by Duy Tùng:

=LET(a,XLOOKUP(B2:B16,D2:D26,E2:E26,""),u,PIVOTBY(A2:A16,a,B2:B16,ARRAYTOTEXT,,0,,0,,a>""),IF(TAKE(u,1)&TAKE(u,,1)="",A1,u))

_x000D_

_x000D_

Excel solution 8 for Filter Values Based on Table, proposed by Sunny Baggu:

=LET(_uid,UNIQUE(A2:A16),_headers,TOROW(UNIQUE(E2:E26)),_col1,TOCOL(IFNA(_uid&_headers,_uid),,TRUE),
_tbl,HSTACK(A2:A16&XLOOKUP(B2:B16,D2:D26,E2:E26,""),B2:B16),
_op,WRAPCOLS(DROP(REDUCE("",_col1,LAMBDA(a,v,VSTACK(a,ARRAYTOTEXT(FILTER(TAKE(_tbl,,-1),TAKE(_tbl,,1)=v,""))))),1),3),
VSTACK(HSTACK(A1,_headers),HSTACK(_uid,_op)))

_x000D_

_x000D_

Excel solution 9 for Filter Values Based on Table, proposed by Md. Zohurul Islam:

=LET(u,D2:D26,v,E2:E26,p,B2:B16,q,A2:A16,sq,SEQUENCE(ROWS(v)),
w,MAP(u,LAMBDA(x,ARRAYTOTEXT(FILTER(q,p=x,0)))),
a,DROP(REDUCE("",w,LAMBDA(x,y,VSTACK(x,--TEXTSPLIT(y,,", ")))),1),
b,FILTER(a,a<>0),
d,DROP(REDUCE("",b,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,FILTER(p,q=y)),y)))),1),
e,XLOOKUP(DROP(d,,1),u,v,""),
f,PIVOTBY(TAKE(d,,1),e,TAKE(d,,-1),LAMBDA(x,ARRAYTOTEXT(SORT(UNIQUE(x)))),0,0,,0),
g,HSTACK(VSTACK("ID",DROP(TAKE(f,,1),1)),DROP(f,,2)),
g)

_x000D_

_x000D_

Excel solution 10 for Filter Values Based on Table, proposed by Mohamed Helmy:

=LET(
k,FILTER(A2:B16,1-ISNA(XMATCH(B2:B16,D2:D26))),
e,E2:E26,
u,UNIQUE(e),
rr,ROWS(u),
IFERROR(VSTACK(HSTACK(A1,TOROW(u)),HSTACK(UNIQUE(A2:A16), MAKEARRAY(rr,rr,LAMBDA(r,c,LET(
v,FILTER(DROP(k,,1),TAKE(k,,1)=r), ARRAYTOTEXT( TOCOL(IF(XMATCH(v, IF(e=INDEX(u,c),D2:D26,NA())),v,NA()),2))))))),""))

_x000D_
&&

Solving the challenge of Filter Values Based on Table with Power Query

Solving the challenge of Filter Values Based on Table with Excel

Leave a Reply Cancel reply