Count Head Streaks by Alpha

List those alphas which have consecutive streaks of H (H – Head, T – Tail) List the count of consecutive occurrences in their respective columns sorted.

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

Solving the challenge of Count Head Streaks by Alpha with Power Query

Power Query solution 1 for Count Head Streaks by Alpha, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    "HT", 
    {
      "T", 
      each 
        let
          N = Table.RowCount(_)
        in
          Table.AddColumn(Table.RemoveColumns(_, {"HT"}), Text.From(N), (n) => N)
    }, 
    0
  ), 
  Combine = Table.Combine(Table.SelectRows(Group, each [HT] = "H")[T]), 
  Sort = Table.SelectColumns(
    Combine, 
    List.Sort(Table.ColumnNames(Combine), each try Number.From(_) otherwise 0)
  )
in
  Sort

Power Query solution 2 for Count Head Streaks by Alpha, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  GroupedHT = Table.Group(
    Source, 
    {"HT"}, 
    {{"Alpha", each [Alpha]}, {"Count", each Table.RowCount(_), Int64.Type}}, 
    0
  ), 
  ExpandedAlpha = Table.ExpandListColumn(GroupedHT, "Alpha"), 
  FilteredConsecutiveH = Table.SelectRows(ExpandedAlpha, each [Count] > 1 and [HT] = "H"), 
  ReplacedValueHT = Table.ReplaceValue(
    FilteredConsecutiveH, 
    each [HT], 
    each Text.From([Count]), 
    Replacer.ReplaceText, 
    {"HT"}
  ), 
  SortedRowsCount = Table.Sort(ReplacedValueHT, {{"Count", 0}}), 
  PivotedColumnHT = Table.Pivot(
    Table.TransformColumnTypes(SortedRowsCount, {{"HT", type text}}, "hr-HR"), 
    List.Distinct(Table.TransformColumnTypes(SortedRowsCount, {{"HT", type text}}, "hr-HR")[HT]), 
    "HT", 
    "Count"
  )
in
  PivotedColumnHT

Power Query solution 3 for Count Head Streaks by Alpha, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Group = Table.Group(
    Source, 
    "HT", 
    {{"Count", Table.RowCount}, {"Alpha", each [Alpha]}}, 
    GroupKind.Local
  ), 
  Filter = Table.SelectRows(Group, each [HT] = "H")[[Alpha], [Count]], 
  Duplicate = Table.DuplicateColumn(Filter, "Count", "Header"), 
  Expand = Table.ExpandListColumn(Duplicate, "Alpha"), 
  Sort = Table.Sort(Expand, "Header"), 
  DataType = Table.TransformColumnTypes(Sort, {"Header", type text}), 
  Return = Table.Pivot(DataType, List.Distinct(DataType[Header]), "Header", "Count")
in
  Return

Power Query solution 4 for Count Head Streaks by Alpha, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Gen = List.Generate(
    () => [X = 1, Idx = 1], 
    each [Idx] <= List.Count(Source[HT]), 
    each [
      X   = try if Source[HT]{[Idx]} = Source[HT]{[Idx] - 1} then [X] else [X] + 1 otherwise 1, 
      Idx = [Idx] + 1
    ], 
    each [X]
  ), 
  Tabla = Table.FromColumns(Table.ToColumns(Source) & {Gen}, Table.ColumnNames(Source) & {"Gen"}), 
  Group = Table.Group(
    Tabla, 
    {"Gen", "HT"}, 
    {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each [Alpha]}}
  ), 
  Filter = Table.SelectRows(Group, each ([HT] = "H"))[[Count], [All]], 
  Expand = Table.ExpandListColumn(Filter, "All"), 
  Sol = Table.RemoveColumns(
    List.Accumulate(
      {2 .. List.Max(Expand[Count])}, 
      Expand, 
      (s, c) => Table.AddColumn(s, Text.From(c), each if c = [Count] then [Count] else null)
    ), 
    "Count"
  )
in
  Sol

Power Query solution 5 for Count Head Streaks by Alpha, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  gp = Table.Group(
    Fonte, 
    {"HT"}, 
    {{"Alpha", each [Alpha]}, {"Count", each List.Count(_)}}, 
    GroupKind.Local
  ), 
  exp = Table.ExpandListColumn(gp, "Alpha"), 
  fil = Table.SelectRows(exp, each ([HT] = "H"))[[Alpha], [Count]], 
  dup = Table.DuplicateColumn(fil, "Count", "Count-Col"), 
  clas = Table.Sort(dup, {{"Count", Order.Ascending}}), 
  res = Table.Pivot(
    Table.TransformColumnTypes(clas, {{"Count", type text}}, "pt-BR"), 
    List.Distinct(Table.TransformColumnTypes(clas, {{"Count", type text}}, "pt-BR")[Count]), 
    "Count", 
    "Count-Col"
  )
in
  res

Power Query solution 6 for Count Head Streaks by Alpha, proposed by Alexis Olson:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 GroupLocal = Table.Group(Source, {"HT"}, {{"Alpha", each [Alpha]}, {"Count", Table.RowCount}}, GroupKind.Local),
 FilterH = Table.SelectRows(GroupLocal, each [HT] = "H"),
 ExpandList = Table.ExpandListColumn(FilterH, "Alpha"),
 RemoveHT = Table.RemoveColumns(ExpandList, {"HT"}),
 AddPivotCol = Table.AddColumn(RemoveHT, "Pivot", each Number.ToText([Count])),
 PivotColumn = Table.Pivot(AddPivotCol, List.Sort(List.Distinct(AddPivotCol[Pivot])), "Pivot", "Count")
in
 PivotColumn

Edit: It looks like I largely reinvented independently what Aditya Kumar Pinjala already posted.

Power Query solution 7 for Count Head Streaks by Alpha, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped Rows" = Table.Group(
    Source, 
    {"HT"}, 
    {
      {"Detail", (a) => Table.AddColumn(a, Text.From(Table.RowCount(a)), each Table.RowCount(a))}, 
      {"Count", each Table.RowCount(_)}
    }, 
    0
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([HT] = "H")), 
  #"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Count", Order.Ascending}}), 
  #"Expanded Detail" = Table.ExpandTableColumn(
    #"Sorted Rows", 
    "Detail", 
    {"Alpha", "HT", "2", "3", "4", "5"}, 
    {"Alpha", "HT.1", "2", "3", "4", "5"}
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Expanded Detail", {"HT", "HT.1", "Count"}), 
  #"Sorted Rows1" = Table.Sort(#"Removed Columns", {{"Alpha", Order.Ascending}})
in
  #"Sorted Rows1"

Solving the challenge of Count Head Streaks by Alpha with Excel

Excel solution 1 for Count Head Streaks by Alpha, proposed by Bo Rydobon 🇹🇭:

=LET(a,A2:A27,s,SCAN(0,B2:B27,LAMBDA(a,v,IF(v="H",IF(a,a,IFNA(XMATCH("T",v:B27)-1,))))),f,FILTER(s,s),
u,TOROW(VSTACK(A1,SORT(UNIQUE(f)))),VSTACK(u,IF(u=A1,FILTER(a,s),IF(u=f,u,""))))

Excel solution 2 for Count Head Streaks by Alpha, proposed by Rick Rothstein:

=LET(a,IFNA(TEXTSPLIT(TEXTJOIN(" ",,MAP(TEXTSPLIT(TRIM(CONCAT(IF(B2:B28<>"H","/",A2:A28&" "))),,"/"),LAMBDA(x,LET(n,LEN(x)-LEN(SUBSTITUTE(x," ","")),TRIM(SUBSTITUTE(x," ",REPT("-",n)&n&" ")))))),"-"," "),""),VSTACK(HSTACK("Alpha",SEQUENCE(,MAX(IFERROR(0+a,)))),a))
The following modification allows for the Alpha column to contain words (names) with spaces between them...
=LET(a,IFNA(TEXTSPLIT(TEXTJOIN(" ",,MAP(TEXTSPLIT(TRIM(CONCAT(IF(B2:B28<>"H","/",SUBSTITUTE(A2:A28," ",CHAR(160))&" "))),,"/"),LAMBDA(x,LET(n,LEN(x)-LEN(SUBSTITUTE(x," ","")),TRIM(SUBSTITUTE(x," ",REPT("-",n)&n&" ")))))),"-"," "),""),VSTACK(HSTACK("Alpha",SEQUENCE(,MAX(IFERROR(0+a,)))),SUBSTITUTE(a,CHAR(160)," ")))

Excel solution 3 for Count Head Streaks by Alpha, proposed by 🇰🇷 Taeyong Shin:

=LET(d,B2:B27,n,SCAN(0,d<>DROP(VSTACK(@+d,d),-1),SUM),m,MMULT(XMATCH(n,n,,{1,-1}),{-1;1})+1,PIVOTBY(A2:A27,m,m,SUM,,0,,0,,d="H"))

Excel solution 4 for Count Head Streaks by Alpha, proposed by Kris Jaganah:

=LET(a,A2:A27,b,B2:B27,c,SCAN("",b,LAMBDA(x,y,IF(y="H",1+x,0))),d,FILTER(HSTACK(a,b,c),c>0),e,SCAN(0,TAKE(d,,-1),LAMBDA(v,w,IF(w=1,1+v,v))),f,UNIQUE(e),g,MAP(f,LAMBDA(z,MAX((e=z)*TAKE(d,,-1)))),h,XLOOKUP(e,f,g),i,TOROW(SORT(UNIQUE(g))),j,XLOOKUP(TAKE(d,,1)&i,TAKE(d,,1)&h,h,""),HSTACK(VSTACK("Alpha",TAKE(d,,1)),VSTACK(TEXT(i,"0"),j)))

Excel solution 5 for Count Head Streaks by Alpha, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _d, A2:B27,
 _al, TAKE(_d, , 1),
 _ht, TAKE(_d, , -1),
 _c1, DROP(_ht, 1) <> DROP(_ht, -1),
 _rc, VSTACK(1, SCAN(1, _c1, LAMBDA(a, b, a + b))),
 _fq, MAP(_rc, LAMBDA(a, SUM(N(_rc = a)))),
 _f, FILTER(HSTACK(_al, _fq), _ht = "H"),
 _fal, TAKE(_f, , 1),
 _ffq, TAKE(_f, , -1),
 _ufq, TOROW(SORT(UNIQUE(_ffq))),
 _c2, XLOOKUP(_fal & "-" & _ufq, _fal & "-" & _ffq, _ffq, ""),
 _h, HSTACK("Alpha", _ufq & ""),
 _r, VSTACK(_h, HSTACK(_fal, _c2)),
 _r
)

Excel solution 6 for Count Head Streaks by Alpha, proposed by Oscar Mendez Roca Farell:

=LET(_b, B2:B27,_f, ROW(_b),_u, UNIQUE( FREQUENCY(IF(_b="H", _f), IF(_b="T", _f)), ,1),_s, SEQUENCE( , 4, 2),_m, REPT( TOROW( ORDER(_u)), _s=TOCOL(IFS(SEQUENCE(,MAX(_u))<=_u, _u), 3)), VSTACK( HSTACK(A1,_s), HSTACK( FILTER(A2:A27, _b="H"),IFERROR(-- _m,""))))

Excel solution 7 for Count Head Streaks by Alpha, proposed by LEONARD OCHEA 🇷🇴:

=LET(al,A2:A27,ht,B2:B27,m_1,(ht="H")*1,m_2,MAP(SEQUENCE(ROWS(al))-1,LAMBDA(a,MATCH(0,DROP(m_1,a),0)-1)),m_3,SCAN(0,m_2,LAMBDA(a,b,IF(b=0,0,IF(a>b,a,b)))),alf,FILTER(al,m_1),m_4,FILTER(m_3,m_3),he,TOROW(SORT(UNIQUE(m_4))),hef,HSTACK("Alpha",he),mf,IF(he=m_4,m_4,""),VSTACK(hef,HSTACK(alf,mf)))

Excel solution 8 for Count Head Streaks by Alpha, proposed by Mohamed Helmy:

=LET(
a,A2:A27,
b,B2:B27,
k,SCAN(0,b="h",LAMBDA(a,d,IF(d,a+1))),
l,SCAN(0,k,LAMBDA(a,d,IF(d,a,a+1))),
e,DROP(REDUCE(0,UNIQUE(l),LAMBDA(a,d,
VSTACK(a,HSTACK(d,MAX(FILTER(k,l=d)))))),1),
f,INDEX(e,,2),
o,TOROW(SORT(FILTER(f,f))),
r,FILTER(a,b="h"),
v,XLOOKUP(r,a,VLOOKUP(l,e,2,0)),
VSTACK(HSTACK(A1,o),HSTACK(r,IF(o=v,v,""))))

&&&

Solving the challenge of Count Head Streaks by Alpha with Power Query

Solving the challenge of Count Head Streaks by Alpha with Excel

Leave a Reply Cancel reply