Equalize Group Row Count

Insert new rows for Groups to make row count for all groups equal. Seq for new rows will be n.1, n.2….where n is seq for the previous row. B appears 4 times which is the maximum. Hence, all groups need to appear 4 times. A already has 2 rows having Seq 1 & 2. Hence, 2 new rows need to be inserted to make count equal to 4 for Group A and their Seq will be 2.1 & 2.2.

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

Solving the challenge of Equalize Group Row Count with Power Query

Power Query solution 1 for Equalize Group Row Count, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    "Group", 
    {
      {"M", each Table.RowCount(_)}, 
      {"T", each [Seq] & List.Transform({1 .. 9}, (n) => List.Max([Seq]) + n / 10)}
    }
  ), 
  Combine = Table.Combine(
    Table.TransformRows(
      Group, 
      each Table.FromColumns(
        {List.FirstN([T], List.Max(Group[M])), List.Repeat({[Group]}, List.Max(Group[M]))}, 
        Table.ColumnNames(Source)
      )
    )
  )
in
  Combine

Power Query solution 2 for Equalize Group Row Count, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Groups"]}[Content], 
  GroupedByGroup = Table.Group(
    Source, 
    {"Group"}, 
    {
      {"CountRows", each Table.RowCount(_), Int64.Type}, 
      {"All", each Table.AddIndexColumn(Table.Sort(_, {{"Seq", Order.Descending}}), "Order", 1)}
    }
  ), 
  AddedCountMissingRows = Table.AddColumn(
    GroupedByGroup, 
    "CountMissingRows", 
    each List.Max(GroupedByGroup[CountRows]) - [CountRows]
  ), 
  ExpandedAll = Table.ExpandTableColumn(
    AddedCountMissingRows, 
    "All", 
    {"Seq", "Order"}, 
    {"Seq", "Order"}
  ), 
  PrepareMissingRows = Table.AddColumn(
    ExpandedAll, 
    "MissingRows", 
    each if [Order] = 1 then {1 .. [CountMissingRows] + 1} else {1}
  )[[Seq], [Group], [MissingRows]], 
  SortedRows = Table.Sort(PrepareMissingRows, {{"Seq", Order.Ascending}}), 
  ExpandedMissingRows = Table.ExpandListColumn(SortedRows, "MissingRows"), 
  RenamedSeq = Table.RenameColumns(ExpandedMissingRows, {{"Seq", "SeqOld"}}), 
  AddedNewSeq = Table.AddColumn(
    RenamedSeq, 
    "Seq", 
    each [SeqOld] + (if [MissingRows] > 1 then [MissingRows] - 1 else 0) / 10
  )[[Seq], [Group]]
in
  AddedNewSeq

Power Query solution 3 for Equalize Group Row Count, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Group = Table.Group(Source, "Group", {{"Count", Table.RowCount}, {"All", each [Seq]}}), 
  Max = List.Max(Group[Count]), 
  Calc = Table.AddColumn(
    Group, 
    "Seq", 
    each [
      Cnt  = Max - [Count], 
      Last = List.Last([All]), 
      Seq  = [All] & List.Transform({1 .. Cnt}, (f) => Last + f / 10)
    ][Seq]
  ), 
  Expand = Table.SelectColumns(Table.ExpandListColumn(Calc, "Seq"), Table.ColumnNames(Source))
in
  Expand

Power Query solution 4 for Equalize Group Row Count, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Grouped = Table.Group(
    Source, 
    {"Group"}, 
    {{"Count", each Table.RowCount(_)}, {"Seq", each Table.ToColumns(_)}}
  ), 
  Proceso = Table.AddColumn(
    Grouped, 
    "New", 
    each [
      a = List.Max(Grouped[Count]), 
      b = List.Transform, 
      c = List.Repeat(List.Distinct(b([Seq], each _){1}), a), 
      d = b([Seq], (x) => List.Combine({x} & {b({1 .. a - [Count]}, each List.Last(x) + _ / 10)})){
        0
      }, 
      e = Table.FromColumns({d, c}, Table.ColumnNames(Source))
    ][e]
  )[[New]], 
  Sol = Table.ExpandTableColumn(Proceso, "New", Table.ColumnNames(Source))
in
  Sol

Power Query solution 5 for Equalize Group Row Count, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  gp = Table.Group(
    Fonte, 
    {"Group"}, 
    {
      {
        "Contagem", 
        each [
          a = _, 
          b = Table.FromRecords({[Seq = null, Group = null]}), 
          c = 4 - Table.RowCount(a), 
          d = Table.Repeat(b, c), 
          e = Table.AddIndexColumn(
            Table.SelectRows(Table.Combine({a, d}), each [Seq] = null), 
            "SEQ", 
            1, 
            1
          ), 
          f = Table.Combine({a, e})
        ][f]
      }
    }
  )[[Contagem]], 
  exp = Table.ExpandTableColumn(gp, "Contagem", {"Seq", "Group", "SEQ"}, {"Seq.1", "Group", "SEQ"}), 
  pa = Table.FillDown(exp, {"Seq.1", "Group"}), 
  result = Table.AddColumn(
    pa, 
    "Seq", 
    each if [SEQ] <> null then Number.From(Text.From([Seq.1]) & "." & Text.From([SEQ])) else [Seq.1]
  )[[Seq], [Group]]
in
  result

Power Query solution 6 for Equalize Group Row Count, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Group"}, 
    {
      {"Count", each Table.RowCount(_), Int64.Type}, 
      {"All", each _, type table [Seq = number, Group = text]}
    }
  ), 
  AddIndexCol = Table.RemoveColumns(
    Table.AddColumn(Group, "AddIndex", each Table.AddIndexColumn([All], "Index", 1, 1)), 
    {"Group", "All"}
  ), 
  MaxCount = List.Max(AddIndexCol[Count]), 
  AddMaxIndexCol = Table.AddColumn(AddIndexCol, "MaxIndex", each List.Max([AddIndex][Index])), 
  AddMaxSeqCol = Table.AddColumn(AddMaxIndexCol, "MaxSeq", each List.Max([AddIndex][Seq])), 
  AddListCol = Table.AddColumn(AddMaxSeqCol, "AddList", each {1 .. MaxCount}), 
  Expand = Table.ExpandListColumn(AddListCol, "AddList"), 
  ExpandedAddIndex = Table.ExpandTableColumn(Expand, "AddIndex", {"Seq", "Group"}, {"Seq", "Group"}), 
  AddIncrement = Table.AddColumn(
    ExpandedAddIndex, 
    "Increment", 
    each if [AddList] <= [MaxIndex] then [Seq] else [MaxSeq] + (([AddList] - [MaxIndex]) * 0.1)
  ), 
  RemoveOther = Table.SelectColumns(AddIncrement, {"Group", "Increment"}), 
  DeDupe = Table.ReorderColumns(
    Table.RenameColumns(Table.Distinct(RemoveOther), {"Increment", "Seq"}), 
    {"Seq", "Group"}
  )
in
  DeDupe

Power Query solution 7 for Equalize Group Row Count, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Mode = List.Count(List.Select(Source[Group], each _ = List.Mode(Source[Group]))), 
  Group = Table.Group(
    Source, 
    {"Group"}, 
    {
      {
        "Seq", 
        each [Seq]
          & List.Transform({1 .. Mode - List.Count([Seq])}, (a) => List.Max([Seq]) + a / 10)
      }
    }
  ), 
  Expand = Table.ExpandListColumn(Group, "Seq"), 
  Reorder = Table.ReorderColumns(Expand, {"Seq", "Group"})
in
  Reorder

Power Query solution 8 for Equalize Group Row Count, proposed by Krzysztof Kominiak:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Expand = Table.ExpandListColumn(
    Table.Group(
      Source, 
      {"Group"}, 
      {
        {"Count", each List.Max(_[Seq])}, 
        {"NTabs", each {1 .. 4 - List.Max(Table.AddIndexColumn(_, "Id", 1, 1)[Id])}}
      }
    ), 
    "NTabs"
  ), 
  Filter = Table.SelectRows(Expand, each ([NTabs] <> null)), 
  MergeCols = Table.CombineColumns(
    Table.TransformColumnTypes(Filter, {{"Count", type text}, {"NTabs", type text}}), 
    {"Count", "NTabs"}, 
    Combiner.CombineTextByDelimiter(",", QuoteStyle.None), 
    "Seq"
  ), 
  AppQuery = Table.Combine({Source, MergeCols}), 
  ChanLocType = Table.TransformColumnTypes(AppQuery, {{"Seq", type number}}, "de-AT"), 
  Result = Table.Sort(ChanLocType, {{"Seq", Order.Ascending}})
in
  Result

Power Query solution 9 for Equalize Group Row Count, proposed by Udit Chatterjee:

let
 
 Source = #"PQChallenge-57",
 
 groupedResult = Table.Group(
 Source,
 {"Group"},
 {{"CountRows", each Table.RowCount(_), Int64.Type}, {"MaxSeq", each List.Max([Seq]), type nullable number}}
 ),
 
 maxCount = List.Max(Table.Column(groupedResult, "CountRows")),
 rowsToAdd = Table.AddColumn(groupedResult, "Rows To Add", each maxCount - [CountRows], Int64.Type),
 unequalGroups = Table.SelectRows(rowsToAdd, each ([Rows To Add] <> 0)),
 
 addedExtraSeqs = Table.AddColumn(unequalGroups, "Add Seq", each List.Numbers([MaxSeq] + 0.1, [Rows To Add], 0.1)),
 keepReqColumns = Table.SelectColumns(addedExtraSeqs, {"Group", "Add Seq"}),
 tableToAdd = Table.ExpandListColumn(keepReqColumns, "Add Seq"),
 renameColumn = Table.RenameColumns(tableToAdd, {{"Add Seq", "Seq"}}),
 datatypeChange = Table.TransformColumnTypes(renameColumn, {{"Seq", type number}}),
 
 combinedWithSource = Table.Combine({Source, datatypeChange}),
 sortedRows = Table.Sort(combinedWithSource, {{"Seq", Order.Ascending}})
in
 sortedRows
Adding image since my solution exceeded the character limits because of extra comments I have added in the code:

Solving the challenge of Equalize Group Row Count with Excel

Excel solution 1 for Equalize Group Row Count, proposed by Bo Rydobon 🇹🇭:

=LET(b,B2:B11,m,MAX(COUNTIF(b,b)),REDUCE(A1:B1,UNIQUE(b),
LAMBDA(c,v,LET(n,FILTER(A2:A11,b=v),VSTACK(c,EXPAND(TAKE(VSTACK(n,MAX(n)+SEQUENCE(m)/10),m),,2,v))))))

Excel solution 2 for Equalize Group Row Count, proposed by محمد حلمي:

=REDUCE(A1:B1,UNIQUE(B2:B11),LAMBDA(a,
d,LET(v,FILTER(A2:B11,B2:B11=d),
k,@DROP(v,,1),
VSTACK(a,IFNA(HSTACK(TAKE(v,,1),EXPAND(k,4,,k)),
TAKE(MAX(v)+
VSTACK(SEQUENCE(ROWS(v))*0,{0.1;0.2;0.3}),4))))))

Excel solution 3 for Equalize Group Row Count, proposed by محمد حلمي:

=LET(
b,B2:B11,
u,UNIQUE(b),
HSTACK(REDUCE(A1,u,LAMBDA(a,d,LET(
v,FILTER(A2:A11,b=d),
VSTACK(a,TAKE(
VSTACK(v,SEQUENCE(4,,MAX(v)+0.1,0.1)),4))))),
VSTACK(B1,TOCOL(IFNA(u,SEQUENCE(,4))))))

Excel solution 4 for Equalize Group Row Count, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _d, A2:B11,
 _h, A1:B1,
 _gp, TAKE(_d, , -1),
 _ugp, UNIQUE(_gp),
 _sq, TAKE(_d, , 1),
 _mx, MAX(COUNTIFS(_gp, _ugp)),
 _e, LAMBDA(a, b,
 LET(
 fsq, FILTER(_sq, _gp = b),
 asq, MAX(fsq) + SEQUENCE(_mx) / 10,
 sq, TAKE(VSTACK(fsq, asq), _mx),
 exp, HSTACK(sq, EXPAND(b, _mx, , b)),
 r, VSTACK(a, exp),
 r
 )
 ),
 _r, REDUCE(_h, _ugp, _e),
 _r
)

Excel solution 5 for Equalize Group Row Count, proposed by Sunny Baggu:

=LET(_seq,A2:A11,_grp,B2:B11,
_u,UNIQUE(_grp),_cnt,COUNTIFS(_grp,_u),_cntmax,MAX(_cnt),_diff,_cntmax-_cnt,_f,XLOOKUP(_u,_grp,_seq),_l,XLOOKUP(_u,_grp,_seq,,,-1),
_l1,MAP(_cnt,_f,LAMBDA(a,b,CONCAT(SEQUENCE(,a,b)&", "))),
_l2,MAP(_cnt,_diff,_l,LAMBDA(a,b,c,CONCAT(IF(b>0,c&"."&SEQUENCE(,b),1/0)&", "))),
_seq1,TEXTSPLIT(TEXTJOIN("",TRUE,TOCOL(HSTACK(_l1,_l2),3)),,", ",TRUE),
_grp1,TOCOL(IFNA(_u,SEQUENCE(,_cntmax))),
VSTACK({"Seq","Group"},HSTACK(_seq1,_grp1)))

&&&

Solving the challenge of Equalize Group Row Count with Power Query

Solving the challenge of Equalize Group Row Count with Excel

Leave a Reply Cancel reply