Home » Show Unique Assignee Priorities

Show Unique Assignee Priorities

Generate the result table from problem table where Assignee and Priority are one below the other. Only unique entries need to be shown.

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

Solving the challenge of Show Unique Assignee Priorities with Power Query

Power Query solution 1 for Show Unique Assignee Priorities, proposed by Bo Rydobon 🇹🇭: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Unpivot = Table.UnpivotOtherColumns(Table.Sort(Table.Distinct(Source),Table.ColumnNames(Source)), {}, "A", "V")[V]
in
 Unpivot

Or

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Ans = List.Combine(List.Zip(Table.ToColumns(Table.Sort(Table.Distinct(Source),Table.ColumnNames(Source)))))
in
 Ans
Power Query solution 2 for Show Unique Assignee Priorities, proposed by Zoran Milokanović: 
let
  Source   = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Solution = List.Combine(List.Distinct(Table.ToRows(Table.Sort(Source, {"Assignee", "Priority"}))))
in
  Solution
Power Query solution 3 for Show Unique Assignee Priorities, proposed by Kris Jaganah: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Merged Columns" = Table.CombineColumns(
    Source, 
    {"Assignee", "Priority"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "Merged"
  ), 
  #"Removed Duplicates" = Table.Distinct(#"Merged Columns"), 
  #"Sorted Rows" = Table.Sort(#"Removed Duplicates", {{"Merged", Order.Ascending}}), 
  #"Split Column by Position" = Table.SplitColumn(
    #"Sorted Rows", 
    "Merged", 
    Splitter.SplitTextByRepeatedLengths(2), 
    {"Assignee", "Priority"}
  ), 
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(
    #"Split Column by Position", 
    {}, 
    "Attribute", 
    "Result"
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns", {"Attribute"})
in
  #"Removed Columns"
Power Query solution 4 for Show Unique Assignee Priorities, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Sol = List.Combine(Table.ToRows(Table.Sort(Table.Distinct(Source), {"Assignee","Priority"})))
in
 Sol

or

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 RemDup = Table.Distinct(Source),
 Sort = Table.Sort(RemDup,{"Assignee","Priority"}),
 Sol = List.Combine(Table.ToRows(Sort))
in
 Sol
Power Query solution 5 for Show Unique Assignee Priorities, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  dis   = Table.Sort(Table.Distinct(Fonte), {"Assignee", "Priority"}), 
  res   = List.Combine(Table.ToRows(dis))
in
  res
Power Query solution 6 for Show Unique Assignee Priorities, proposed by Brian Julius: 
let
  Source = Table.Distinct(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]), 
  Sort = Table.Sort(Source, {{"Assignee", Order.Ascending}, {"Priority", Order.Ascending}}), 
  AddIndex = Table.AddIndexColumn(Sort, "Index", 0, 1, Int64.Type), 
  UnpivotOther = Table.SelectColumns(
    Table.UnpivotOtherColumns(AddIndex, {"Index"}, "Attribute", "Result"), 
    "Result"
  )
in
  UnpivotOther
Power Query solution 7 for Show Unique Assignee Priorities, proposed by Eric Laforce: 
let
  Source          = Excel.CurrentWorkbook(){[Name = "tData82"]}[Content], 
  Sort            = Table.Sort(Source, {"Assignee", "Priority"}), 
  TransformToList = List.Combine(Table.ToRows(Table.Distinct(Sort))), 
  ToTable         = Table.FromColumns({TransformToList}, {"Result"})
in
  ToTable
Power Query solution 8 for Show Unique Assignee Priorities, proposed by Victor Wang: 
let
  Source       = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  distinctRows = Table.Distinct(Source), 
  toRows       = Table.ToRows(distinctRows), 
  Sort         = List.Sort(toRows, {{each _{0}}, {each _{1}}}), 
  Combine      = List.Combine(Sort)
in
  Combine
Power Query solution 9 for Show Unique Assignee Priorities, proposed by Sandeep Marwal: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Removed Duplicates" = Table.Distinct(Source), 
  #"Sorted Rows" = Table.Sort(
    #"Removed Duplicates", 
    {{"Assignee", Order.Ascending}, {"Priority", Order.Ascending}}
  ), 
  Custom1 = Table.ToRows(#"Sorted Rows"), 
  Custom2 = List.Combine(Custom1)
in
  Custom2
Power Query solution 10 for Show Unique Assignee Priorities, proposed by Henriette Hamer: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Removed Duplicates" = Table.Distinct(Source), 
  #"Sorted Rows" = Table.Sort(
    #"Removed Duplicates", 
    {{"Assignee", Order.Ascending}, {"Priority", Order.Ascending}}
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    #"Added Index", 
    {"Index"}, 
    "Attribute", 
    "Value"
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns", {"Index", "Attribute"})
in
  #"Removed Columns"
Power Query solution 11 for Show Unique Assignee Priorities, proposed by Gráinne Duggan: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  SortedZip = Table.Sort(
    Table.Distinct(Source), 
    {{"Assignee", Order.Ascending}, {"Priority", Order.Ascending}}
  ), 
  ToTable = Table.FromList(
    List.Zip({SortedZip[Assignee], SortedZip[Priority]}), 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ), 
  Result = Table.ExpandListColumn(ToTable, "Column1")
in
  Result

Solving the challenge of Show Unique Assignee Priorities with Excel

Excel solution 1 for Show Unique Assignee Priorities, proposed by Bo Rydobon 🇹🇭: 
=TOCOL(SORT(UNIQUE(A2:B13),{1,2}))
Excel solution 2 for Show Unique Assignee Priorities, proposed by Rick Rothstein: 
=TEXTSPLIT(TEXTJOIN(" ",,SORT(UNIQUE(A2:A13&" "&B2:B13))),," ")
Excel solution 3 for Show Unique Assignee Priorities, proposed by محمد حلمي: 
=TOCOL(SORT(UNIQUE(A2:B13),{1,2}))
Excel solution 4 for Show Unique Assignee Priorities, proposed by 🇰🇷 Taeyong Shin: 
=TOCOL(GROUPBY(A2:B13,,,,0))
Excel solution 5 for Show Unique Assignee Priorities, proposed by Kris Jaganah: 
=REDUCE("Result",UNIQUE(SORT(A2:A13&" "&B2:B13)),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,," "))))
Excel solution 6 for Show Unique Assignee Priorities, proposed by Sunny Baggu: 
=TOCOL(UNIQUE(SORT(A2:B13,{1,2})))
Excel solution 7 for Show Unique Assignee Priorities, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=LET(q;UNIQUE(HSTACK(A2:A13;B2:B13));LET(e;SEQUENCE(COUNTA(q));w;SORTBY(q;TAKE(q;;1);1;TAKE(q;;-1);1);IFERROR(XLOOKUP(e;LET(y;LET(x;e;IF(MOD(x;2)=1;x;""));FILTER(y;y<>""));TAKE(w;;1));XLOOKUP(e;LET(y;LET(x;e;IF(MOD(x;2)=0;x;""));FILTER(y;y<>""));TAKE(w;;-1)))))
Excel solution 8 for Show Unique Assignee Priorities, proposed by Daniel Garzia: 
=LET(d,SORT(UNIQUE(A2:B13),{1,2}),r,ROWS(d)*2,INDEX(d,SEQUENCE(r,,,1/2),1+MOD(SEQUENCE(r,,0),2)))

Solving the challenge of Show Unique Assignee Priorities with Python in Excel

Python in Excel solution 1 for Show Unique Assignee Priorities, proposed by Alejandro Campos: 
result = xl("A1:B13", headers=True).drop_duplicates().sort_values(
 ['Assignee', 'Priority']).values.flatten()
result

&&&

Leave a Reply