Split Full Names into First and Last Using Power Query

Group the staff and insert the group name after the last count. The end of a group is always before No. 1. The last group has only 1 staff. Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solution

📌 Challenge Details and Links
Challenge Number: 66
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Insert Group Name with Power Query

Power Query solution 1 for Insert Group Name, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content][Staff], 
  _ = List.TransformMany(
    List.Positions(Source), 
    each {Source{_}}
      & {
        {}, 
        {"GROUP " & Text.From(List.Count(List.Select(List.FirstN(Source, _ + 1), each _ = 1)))}
      }{Byte.From((Source{_ + 1}? ?? 1) = 1)}, 
    (i, _) => _
  )
in
  _

Power Query solution 2 for Insert Group Name, proposed by Kris Jaganah:

let
  A = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content][Staff], 
  B = List.Count(A), 
  C = List.Skip(List.PositionOf(A, 1, 2) & {B}), 
  D = List.Accumulate(
    List.Zip({C, List.Positions(C)}), 
    A, 
    (v, w) => List.InsertRange(v, w{0} + w{1}, {"GROUP " & Text.From(w{1} + 1)})
  ), 
  E = Table.FromColumns({D}, {"Groups"})
in
  E

Power Query solution 3 for Insert Group Name, proposed by Kris Jaganah:

let
  A = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content][Staff], 
  B = List.Accumulate(A, {0}, (x, y) => x & {if y = 1 then List.Last(x) + 1 else List.Last(x)}), 
  C = List.RemoveNulls(
    List.Combine(
      List.Transform(
        List.Positions(B), 
        each {
          "GROUP "
            & Text.From(try if A{_} = 1 and B{_} <> 0 then B{_} else null otherwise List.Last(B)), 
          A{_}?
        }
      )
    )
  ), 
  D = Table.FromColumns({C}, {"Groups"})
in
  D

Power Query solution 4 for Insert Group Name, proposed by Kris Jaganah:

let
  A = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content][Staff], 
  B = List.PositionOf(A, 1, 2), 
  C = Table.FromColumns(
    {
      List.TransformMany(
        List.Positions(B), 
        each List.Range(A, B{_}, B{_ + 1}? - B{_}) & {"Group " & Text.From(_ + 1)}, 
        (x, y) => y
      )
    }, 
    {"Groups"}
  )
in
  C

Power Query solution 5 for Insert Group Name, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content], 
  Grp = Table.Group(Source, "Staff", {{"A", each [Staff]}}, 0, (a, b) => Number.From(b = 1))[A], 
  Lista = List.Combine(
    List.Transform({1 .. List.Count(Grp)}, each Grp{_ - 1} & {"GROUP " & Text.From(_)})
  ), 
  Sol = Table.FromColumns({Lista}, {"Groups"})
in
  Sol

Power Query solution 6 for Insert Group Name, proposed by Luan Rodrigues:

let
  grp = Table.Group(
    tblAppointment, 
    "Staff", 
    {{"tab", each _[Staff]}}, 
    0, 
    (a, b) => Number.From(b = 1)
  )[tab], 
  res = List.Combine(
    List.Combine(
      List.Zip({grp, List.Transform(List.Positions(grp), each {"GROUP" & Text.From(_ + 1)})})
    )
  )
in
  res

Power Query solution 7 for Insert Group Name, proposed by Brian Julius:

let
  Source = Table.TransformColumnTypes(
    Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content], 
    {"Staff", Text.Type}
  ), 
  AddGpIdx = Table.FillDown(
    Table.AddColumn(
      Table.AddIndexColumn(Source, "Index", 1), 
      "GpIdx", 
      each if [Staff] = "1" then [Index] else null
    ), 
    {"GpIdx"}
  ), 
  Group = Table.AddIndexColumn(
    Table.RemoveColumns(
      Table.Group(AddGpIdx, {"GpIdx"}, {{"All", each _}, {"Count", each Table.RowCount(_)}}), 
      "GpIdx"
    ), 
    "Group", 
    1, 
    1
  ), 
  Expand = Table.ExpandTableColumn(Group, "All", {"Staff"}, {"Staff"}), 
  AddGroups = Table.AddColumn(Expand, "Groups", each "GROUP" & Text.From([Group])), 
  Regroup = Table.Group(AddGroups, {"Group"}, {{"All", each _}}), 
  AddGp = Table.AddColumn(
    Regroup, 
    "Groups", 
    each [
      x = [All], 
      a = List.Max(x[Count]), 
      b = List.Max(x[Groups]), 
      d = Table.SelectColumns(x, "Staff"), 
      e = Table.InsertRows(d, a, {[Staff = b]})
    ][e]
  ), 
  RemCols = Table.SelectColumns(AddGp, {"Groups"}), 
  Exp2 = Table.ExpandTableColumn(RemCols, "Groups", {"Staff"}, {"Groups"})
in
  Exp2

Power Query solution 8 for Insert Group Name, proposed by Abdallah Ally:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content], 
  Group = Table.Group(Source, "Staff", {"Data", each _}, 0, (x, y) => Byte.From(y = 1)), 
  Transform = List.Transform(
    {0 .. Table.RowCount(Group) - 1}, 
    each Group[Data]{_}[Staff] & {"GROUP " & Text.From(_ + 1)}
  ), 
  Result = Table.FromColumns({List.Combine(Transform)}, {"Groups"})
in
  Result

Power Query solution 9 for Insert Group Name, proposed by Ramiro Ayala Chávez:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.Group(S, "Staff", {"G", each _}, 0, (x, y) => Number.From(x + 1 <> y and x = y))[[G]], 
  b = Table.TransformColumns(
    a, 
    {"G", each Table.InsertRows(_, Table.RowCount(_), {[Staff = "GROUP"]})}
  ), 
  c = Table.ExpandTableColumn(Table.AddIndexColumn(b, "I", 1), "G", {"Staff"}), 
  d = Table.TransformColumnTypes(c, {{"Staff", type text}, {"I", type text}}), 
  Sol = Table.AddColumn(
    d, 
    "Groups", 
    each if [Staff] = "GROUP" then [Staff] & " " & [I] else [Staff]
  )[[Groups]]
in
  Sol

Power Query solution 10 for Insert Group Name, proposed by Seokho MOON:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content][Staff], 
  Idx = List.PositionOf(Source, 1, 2), 
  Col = List.Transform(List.Positions(Idx), Fun), 
  Fun = each [
    A = List.Range(Source, Idx{_}, (try Idx{_ + 1} otherwise List.Count(Source)) - Idx{_}), 
    B = "Group " & Text.From(_ + 1), 
    C = A & {B}
  ][C], 
  Res = Table.FromColumns({List.Combine(Col)}, {"Groups"})
in
  Res

Power Query solution 11 for Insert Group Name, proposed by Meganathan Elumalai:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content], 
  Result = [
    Lst = List.Skip(List.PositionOf(Source[Staff], 1, Occurrence.All)) & {List.Count(Source[Staff])}, 
    fin = Table.FromColumns(
      {
        List.Accumulate(
          List.Reverse(List.Positions(Lst)), 
          Source[Staff], 
          (s, c) => List.InsertRange(s, Lst{c}, {"Group " & Text.From(c + 1)})
        )
      }, 
      {"Staff"}
    )
  ][fin]
in
  Result

Power Query solution 12 for Insert Group Name, proposed by Peter Krkos:

let
  GroupedRows = Table.AddIndexColumn(
    Table.Group(Source, "Staff", {{"T", each [Staff], type table}}, 0, (x, y) => Byte.From(y <= x)), 
    "i", 
    1
  ), 
  Transformed = Table.FromList(
    List.Combine(
      List.Transform(
        List.Zip({GroupedRows[T], GroupedRows[i]}), 
        each _{0} & {"GROUP " & Text.From(_{1})}
      )
    ), 
    Splitter.SplitByNothing(), 
    {"Groups"}
  )
in
  Transformed

Power Query solution 13 for Insert Group Name, proposed by Yaroslav Drohomyretskyi:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content], 
  Result = List.Accumulate(
    Source[Staff], 
    [List = {}, Prev = null, GroupNum = 0], 
    (state, current) =>
      let
        isNewGroup = state[Prev] <> null and current < state[Prev], 
        newGroupNum = if isNewGroup then state[GroupNum] + 1 else state[GroupNum], 
        updatedList = 
          if isNewGroup then
            state[List] & {"GROUP " & Text.From(newGroupNum)} & {current}
          else
            state[List] & {current}
      in
        [List = updatedList, Prev = current, GroupNum = newGroupNum]
  ), 
  FinalList = Result[List] & {"GROUP " & Text.From(Result[GroupNum] + 1)}
in
  FinalList

Power Query solution 14 for Insert Group Name, proposed by Krzysztof Kominiak:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content], 
  Grp = Table.Group(Source, "Staff", {{"tmp", each _}}, 0, (x, y) => Byte.From(y = 1)), 
  Id = Table.AddIndexColumn(Grp, "Id", 1), 
  Pref = Table.TransformColumns(Id, {{"Id", each "GROUP " & Text.From(_, "pl-PL"), type text}}), 
  Result = Table.RenameColumns(
    Table.Combine(
      Table.AddColumn(
        Pref, 
        "NT", 
        each Table.InsertRows([tmp], Table.RowCount([tmp]), {[Staff = [Id]]})
      )[NT]
    ), 
    {"Staff", "Groups"}
  )
in
  Result

Power Query solution 15 for Insert Group Name, proposed by Aleksandr Mynka:

let
  src = Excel.CurrentWorkbook(){[Name = "tblAppointment"]}[Content], 
  staff = List.Buffer(src[Staff]), 
  cnt = List.Count(staff), 
  acc = List.Generate(
    () => [i = 0, gr = 0, Groups = {staff{0}}], 
    each [i] < cnt, 
    each [
      i = [i] + 1, 
      gr = if staff{i} = 1 then [gr] + 1 else [gr], 
      Groups = 
        if staff{i} = 1 then
          if i <> cnt - 1 then
            {"GROUP " & Text.From(gr), staff{i}}
          else
            {"GROUP " & Text.From(gr), staff{i}, "GROUP " & Text.From(gr + 1)}
        else
          {staff{i}}
    ], 
    each [[Groups]]
  ), 
  tbl = Table.FromRecords(acc), 
  res = Table.ExpandListColumn(tbl, "Groups")
in
  res

Solving the challenge of Insert Group Name with Excel

Excel solution 1 for Insert Group Name, proposed by Bo Rydobon 🇹🇭:

=LET(
   x,
   B4:B14,
   TOCOL(
       HSTACK(
           x,
           IFS(
               DROP(
                   VSTACK(
                       x,
                       1),
                   1)=1,
               "Group"&SCAN(
                   0,
                   x=1,
                   SUM))),
       3))

Excel solution 2 for Insert Group Name, proposed by Rick Rothstein:

=LET(r,
   B4:B14,
   g,
   "Group ",
   v,
   VSTACK,
   c,
   COUNTIF,
   v(REDUCE(,
   r,
   LAMBDA(a,
   x,
   v(a,
   IF(x=1,
   v(g&c((@r):x,
   1)-1,
   x),
   x)))),
   g&c(
       r,
       1)))

Excel solution 3 for Insert Group Name, proposed by Kris Jaganah:

=LET(a,
   tblAppointment[Staff],
   b,
   SCAN(
       ,
       VSTACK(
           0,
           a=1),
       SUM),
   c,
   "GROUP "&IFNA(IF((a=1)*(b>0),
   b,
   zz),
   b),
   TOCOL(
       HSTACK(
           c,
           a),
       3))

Excel solution 4 for Insert Group Name, proposed by Julian Poeltl:

=LET(
   D,
   tblAppointment[Staff],
   G,
   IFNA(
       D>DROP(
           D,
           1),
       1),
   S,
   SCAN(
       0,
       G,
       LAMBDA(
           A,
           B,
           A+B)),
   TOCOL(
       HSTACK(
           D,
           IF(
               G,
               "GROUP "&S,
               X)),
       3))

Excel solution 5 for Insert Group Name, proposed by Hussein SATOUR:

=LET(
   s,
   B4:B14,
   a,
   IFNA(
       s>VSTACK(
           0,
           s),
       0),
   b,
   "GROUP "&ABS(
       SCAN(
           0,
           a-1,
           SUM)),
   DROP(
       TEXTSPLIT(
           CONCAT(
               IF(
                   a,
                   s,
                   b&"/1")&"/"),
           ,
           "/"),
       -2))

Excel solution 6 for Insert Group Name, proposed by Oscar Mendez Roca Farell:

=LET(
   d,
   B4:B14,
   s,
   SCAN(
       0,
       d=1,
       SUM),
   u,
   UNIQUE(
       s),
   SORTBY(
       VSTACK(
           d,
           "GROUP "&u),
       VSTACK(
           s,
           u)))

Excel solution 7 for Insert Group Name, proposed by Duy Tùng:

=LET(
   a,
   SCAN(
       1,
       IFERROR(
           B4:B14-B3:B13,
           1)<>1,
       SUM),
   REDUCE(
       "Groups",
       UNIQUE(
           a),
       LAMBDA(
           x,
           v,
           VSTACK(
               x,
               VSTACK(
                   FILTER(
                       B4:B14,
                       a=v),
                   "GROUP "&v)))))

Excel solution 8 for Insert Group Name, proposed by Sunny Baggu:

=LET(
   
    _a,
    tblAppointment[Staff],
   
    _b,
    N(
        _a = 1),
   
    _c,
    SCAN(
        0,
         _b,
         LAMBDA(
             a,
              v,
              a + v)),
   
    _uc,
    UNIQUE(
        _c),
   
    _d,
    XMATCH(
        _uc,
         _c,
         ,
         -1),
   
    _s,
    SEQUENCE(
        ROWS(
            _a)),
   
    _e,
    XMATCH(
        _s,
         _d),
   
    TOCOL(
        HSTACK(
            _a,
             "GROUP " & _e),
         3,
         0)
   )

Excel solution 9 for Insert Group Name, proposed by 🇵🇪 Ned Navarrete C.:

=LET(
   m,
   B4:B14,
   r,
   B5:B15-m<>1,
   TOCOL(
       HSTACK(
           m,
           "GROUP "&SCAN(
               ,
               r,
               SUM)/r),
       3))

Excel solution 10 for Insert Group Name, proposed by Pieter de B.:

=LET(
   b,
   B4:B14,
   s,
   b=1,
   x,
   SCAN(
       -1,
       s,
       SUM),
   TOCOL(
       VSTACK(
           HSTACK(
               IFS(
                   s*x,
                   "GROUP "&x),
               b),
           "GROUP "&MAX(
               x)+1),
       2))

Excel solution 11 for Insert Group Name, proposed by Hamidi Hamid:

=VSTACK(
   "Groups",
   TOCOL(
       HSTACK(
           B4:B14,
           LET(
               x,
               IF(
                   B4:B14+1<>B5:B15,
                   1,
                   0),
               v,
               IF(
                   x=0,
                   0,
                   "GROUP "&SCAN(
                       0,
                       x,
                       SUM)),
               IF(
                   v=0,
                   1/0,
                   v))),
       3))

Excel solution 12 for Insert Group Name, proposed by Asheesh Pahwa:

=LET(
   sc,
   SCAN(
       0,
       IF(
           B4:B14=1,
           1,
           0),
       LAMBDA(
           x,
           y,
           x+y)),
   u,
   UNIQUE(
       sc),
   REDUCE(
       D3,
       u,
       LAMBDA(
           a,
           v,
           VSTACK(
               a,
               LET(
                   f,
                   FILTER(
                       B4:B14,
                       sc=v),
                   VSTACK(
                       f,
                       "GROUP"&v))))))

Excel solution 13 for Insert Group Name, proposed by Ankur Sharma:

=LET(
   r,
    B4:B14,
   
   a,
    SCAN(
        -1,
         r = 1,
         SUM),
   
   b,
    IF(
        r = 1,
         "Group " & a & ", " & r,
         r),
   
   c,
    TEXTJOIN(
        ", ",
         ,
         b,
         "Group " & MAX(
             a) + 1),
   
   DROP(
       TEXTSPLIT(
           c,
            ,
            ", "),
        1))

Excel solution 14 for Insert Group Name, proposed by Meganathan Elumalai:

=LET(x,
   B4:B14,
   DROP(REDUCE(1,
   VSTACK(
       DROP(
           x,
           1),
       1),
   LAMBDA(a,
   v,
   VSTACK(a,
   IF(v=1,
   VSTACK("Group "&SUM(--(a=1)),
   v),
   v)))),
   -1))

Excel solution 15 for Insert Group Name, proposed by JvdV -:

=TOCOL(
   HSTACK(
       B4:B14,
       IFS(
           B5:B15<2,
           "GROUP "&SCAN(
               ,
               B4:B14=1,
               SUM))),
   3)

Excel solution 16 for Insert Group Name, proposed by Milan Shrimali:

=let(
   A,
   BYROW(
       A4:A18,
       lambda(
           x,
           arrayformula(
               if(
                   and(
                       x=1,
                       row(
                           x)<>4),
                   hstack(
                       x,
                       "Group",
                       row(
                           X)),
                   X)))),
   b,
   unique(
       choosecols(
           A,
           3),
       0,
       1),
   master,
   hstack(
       b,
       arrayformula(
           "Group " & SEQUENCE(
               count(
                   b),
               1,
               1,
               1))),
   fnl,
   hstack(
        choosecols(
            a,
            1),
       byrow(
           choosecols(
               a,
               3),
           lambda(
               x,
               iferror(
                   filter(
                       CHOOSECOLS(
                           master,
                           2),
                       CHOOSECOLS(
                           master,
                           1)=x),
                   "")))),
   rws,
   hstack(
       fnl,
       SEQUENCE(
           count(
               CHOOSECOLS(
                   fnl,
                   1)),
           1,
           1,
           1)),
   final,
   tocol(
       hstack(
           choosecols(
                   fnl,
                   1),
           filter(
               choosecols(
                   rws,
                   2),
               choosecols(
                   rws,
                   3)>1))),
   filter(
       final,
       final<>""))

Excel solution 17 for Insert Group Name, proposed by Fábio Gatti:

=LET(
x,
   B4:B14,
   
Grp,
   SCAN(
       0,
       x,
       LAMBDA(
           a,
           b,
           a+IF(
               b=1,
               1,
               0)))-1,
   
InsGrp,
   IF((x=1)*(Grp>0),
   "GRUPO "&Grp&";"&x,
   x),
   
Join,
   TEXTJOIN(
       ";",
       1,
       InsGrp),
   
Result,
   TEXTSPLIT(
       Join,
       ,
       ";"),
   
Nums,
   IFERROR(
       --Result,
       Result),
   

Nums)

Solving the challenge of Insert Group Name with Python

Python solution 1 for Insert Group Name, proposed by Konrad Gryczan, PhD:

I almost forgot 
import pandas as pd
path = "files/Ex-Challenge 07 2025.xlsx"
input = pd.read_excel(path, usecols="B", skiprows=2, nrows=11)
test = pd.read_excel(path, usecols="D", skiprows=2, nrows=15).astype(str)
input['group'] = (input['Staff'].diff() < 0).cumsum() + 1
input['Staff'] = input['Staff'].astype(str)
result = input.groupby('group', group_keys=False).apply(
 lambda x: x._append(pd.DataFrame({'Staff': [f"GROUP {x.name}"]}))
).reset_index(drop=True)[['Staff']].rename(columns={'Staff': 'Groups'})
print(result.equals(test)) # True

Python solution 2 for Insert Group Name, proposed by Luan Rodrigues:

import pandas as pd
file = "Ex-Challenge 07 2025.xlsx"
df = pd.read_excel(file,usecols="B",skiprows=2).dropna()
df["group"] = "GROUP" + df["Staff"].eq(1).cumsum().astype(str)
grp = (df.groupby('group')
 .apply(lambda x: pd.DataFrame( list(x['Staff']) + [x['group'].iloc[0]],columns=['Valor']))
 .reset_index(drop=True))
print(grp)

Python solution 3 for Insert Group Name, proposed by Abdallah Ally:

import pandas as pd
# Load data from Excel file
file_path = 'Ex-Challenge 07 2025.xlsx'
df = pd.read_excel(io=file_path, usecols='B', skiprows=2, nrows=11)
# Perform data manipulation
ones = [i for i in df.index if df.iat[i, 0] == 1]
zipped = zip(ones, ones[1:] + [len(df)], range(1, len(ones) + 1))
values = [
 v for z in zipped 
 for v in list(df['Staff'])[z[0]: z[1]] + ['GROUP ' + str(z[2])]
]
df = pd.DataFrame(data={'Groups': values})
df

Python solution 4 for Insert Group Name, proposed by Shantanu Tiwari:

import pandas as pd
staff_data = {
 'Staff': [f'Staff {i+1}' for i in range(20)] # 20 staff members as an example
}
# Create a DataFrame
df = pd.DataFrame(staff_data)
group_size = 5
total_staff = len(df)
df['Group'] = ['Group ' + str(i+1) for i in range((total_staff // group_size) + (1 if total_staff % group_size != 0 else 0)) for _ in range(group_size)][:total_staff]
if total_staff % group_size != 0:
 df['Group'].iloc[-(total_staff % group_size):] = 'Group ' + str((total_staff // group_size) + 1)
print(df)

Solving the challenge of Insert Group Name with Python in Excel

Python in Excel solution 1 for Insert Group Name, proposed by Alejandro Campos:

One 
#PythonExcel solution.
staff_list = xl("tblAppointment[Staff]")[0]
groups, current_group, group_count = [], [], 1
for staff in staff_list:
 if staff == 1 and current_group:
 groups.extend([current_group, [f"GROUP {group_count}"]])
 current_group, group_count = [], group_count + 1
 current_group.append(staff)
if current_group:
 groups.extend([current_group, [f"GROUP {group_count}"]])
[item for sublist in groups for item in sublist]

Solving the challenge of Insert Group Name with R

R solution 1 for Insert Group Name, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
path = "files/Ex-Challenge 07 2025.xlsx"
input = read_excel(path, range = "B3:B14")
test = read_excel(path, range = "D3:D18")
result = input %>%
 mutate(group = cumsum(c(1, diff(Staff) < 0)),
 Staff = as.character(Staff)) %>%
 group_by(group) %>%
 group_split() %>%
 imap_dfr(~ {
 dynamic_row <- tibble(
 Staff = paste("GROUP", .y),
 group = unique(.x$group)
 )
 bind_rows(.x, dynamic_row)
 }) %>%
 select(Groups = Staff)
all.equal(result, test)
# [1] TRUE

Solving the challenge of Insert Group Name with Power Query

Solving the challenge of Insert Group Name with Excel

Solving the challenge of Insert Group Name with Python

Solving the challenge of Insert Group Name with Python in Excel

Solving the challenge of Insert Group Name with R

Leave a Reply Cancel reply