Divide the data set for a group in 2 halves. In case of odd number of entries say n, first half will be (n+1)/2 and second half will be (n-1)/2. Find the Running Total for each half separately.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 144
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Split Group and Track Running with Power Query
Power Query solution 1 for Split Group and Track Running, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Group"},
{
{
"All",
(z) =>
let
a = z[Value],
b = List.Count(a),
c = List.RemoveLastN(
List.Generate(
() => [x = 0, y = a{0}],
each [x] <= b,
each [x = [x] + 1, y = if x <> Number.RoundUp(b / 2) then [y] + a{x} else a{x}],
each [y]
)
)
in
Table.FromColumns({a, c}, {"Value", "Running Total"})
}
}
),
Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
SolPower Query solution 2 for Split Group and Track Running, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = List.Combine(
Table.AddColumn(
Table.Group(
Fonte,
{"Group"},
{{"Dividir", each Number.RoundUp(Table.RowCount(_) / 2)}, {"Contagem", each _}}
),
"add",
each Table.Split([Contagem], [Dividir])
)[add]
),
res = Table.Combine(
List.Transform(
gp,
(x) =>
Table.FromColumns(
Table.ToColumns(x)
& {
List.Generate(
() => [acc = x[Value]{0}, i = 0],
each [i] < List.Count(x[Value]),
each [acc = [acc] + x[Value]{[i] + 1}, i = [i] + 1],
each [acc]
)
},
Table.ColumnNames(Fonte) & {"Running Total"}
)
)
)
in
resPower Query solution 3 for Split Group and Track Running, proposed by An Nguyen:
let
lambda = (tb) =>
let
ColName = Table.ColumnNames(tb),
Val = tb[Value],
Length = List.Count(Val),
RunningTotal = List.Generate(
() => [Counter = 0, RunningTotal = Val{0}],
each [Counter] < Length,
each [
Counter = [Counter] + 1,
RunningTotal =
if Counter <> Number.RoundUp(Length / 2) then
[RunningTotal] + Val{Counter}
else
Val{Counter}
],
each [RunningTotal]
),
TableResult = Table.FromColumns({Val, RunningTotal}, {"Value", "Running Total"})
in
TableResult,
MainData = Excel.CurrentWorkbook(){[Name = "raw"]}[Content],
Group = Table.Group(MainData, "Group", {"Pandora", (tb) => lambda(tb)}),
Expand = Table.ExpandTableColumn(Group, "Pandora", {"Value", "Running Total"})
in
ExpandPower Query solution 4 for Split Group and Track Running, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Group"},
{{"Count", each [Custom1 = Table.SplitAt(_, Number.RoundUp(Table.RowCount(_) / 2))][Custom1]}}
),
#"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "Count"),
trn = Table.TransformColumns(
#"Expanded Count",
{
"Count",
each [
#"Added Index" = Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type),
Custom1 = Table.AddColumn(
#"Added Index",
"Sum",
each List.Sum(List.FirstN(#"Added Index"[Value], [Index]))
)
][Custom1]
}
),
#"Expanded Count1" = Table.ExpandTableColumn(trn, "Count", {"Value", "Sum"}, {"Value", "Sum"})
in
#"Expanded Count1"Power Query solution 5 for Split Group and Track Running, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Combine(
Table.Group(
Source,
{"Group"},
{
{
"Data",
each Table.AddColumn(
Table.AddIndexColumn(_, "I", 1),
"C",
(r) =>
let
x = (Number.Round(Table.RowCount(_) / 2, 0, RoundingMode.AwayFromZero))
in
if r[I] <= x then 1 else 2
),
type table
}
}
)[Data]
),
#"Grouped Rows1" = Table.Group(
#"Grouped Rows",
{"Group", "C"},
{
{
"Data",
each
let
values = List.Buffer([Value]),
cond = Table.RowCount(_)
in
Table.FromColumns(
{
[Value],
List.Generate(
() => [I = 0, RT = values{I}],
each [I] < cond,
each [I = [I] + 1, RT = [RT] + values{I}],
each [RT]
)
},
{"Value", "Running Total"}
),
type table
}
}
),
#"Expanded Data" = Table.ExpandTableColumn(
#"Grouped Rows1",
"Data",
{"Value", "Running Total"},
{"Value", "Running Total"}
),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data", {"C"}),
#"Changed Type1" = Table.TransformColumnTypes(
#"Removed Columns",
{{"Value", Int64.Type}, {"Running Total", Int64.Type}}
)
in
#"Changed Type1"Solving the challenge of Split Group and Track Running with Excel
Excel solution 1 for Split Group and Track Running, proposed by Bo Rydobon 🇹🇭:
=LET(a,A2:A16,HSTACK(A2:B16,SCAN(0,a,LAMBDA(c,i,IF(MOD(COUNTIF(A2:i,i)-1,EVEN(COUNTIF(a,i))/2),c)+INDEX(B2:B16,ROWS(A2:i))))))Excel solution 2 for Split Group and Track Running, proposed by محمد حلمي:
=HSTACK(A2:B16,SCAN(0,A2:A16,LAMBDA(a,d,
OFFSET(d,,1)+a*AND(COUNTIF(A2:d,d)<>
VSTACK(1,INT(COUNTIF(A2:A16,d)/2+1.5))))))Excel solution 3 for Split Group and Track Running, proposed by Kris Jaganah:
=LET(a,A2:A16,b,B2:B16,c,COUNTIF(a,a),d,IF(SEQUENCE(ROWS(a))-XMATCH(a,a)>=c/2,2,1),e,b+(d=VSTACK(@d,DROP(d,-1)))/10,VSTACK({"Group","Value","Running Total"},HSTACK(a,b,SCAN(0,e,LAMBDA(x,y,IF(INT(y)=y,y,x+ROUND(y,0)))))))Excel solution 4 for Split Group and Track Running, proposed by Oscar Mendez Roca Farell:
=LET(_g, A2:A16, HSTACK(A2:B16, MAP(_g, B2:B16, LAMBDA(a, b, SUM(OFFSET(b, , ,-1-MOD(COUNTIF(A2:a, a)-1, ROUNDUP(COUNTIF(_g, a)/2, ))))))))Solving the challenge of Split Group and Track Running with Python in Excel
Python in Excel solution 1 for Split Group and Track Running, proposed by Alejandro Campos:
df = xl("A1:B16", headers=True)
result = df.groupby('Group').apply(lambda g: pd.concat([h.assign(Running_Total=h['Value']
.cumsum()) for h in [g.iloc[:(len(g)+1)//2], g.iloc[(len(g)+1)//2:]]])).reset_index(drop=True)
result
Solving the challenge of Split Group and Track Running with R
R solution 1 for Split Group and Track Running, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_144.xlsx", range = "A1:B16")
test = read_excel("Power Query/PQ_Challenge_144.xlsx", range = "E1:G16")
result = input %>%
group_by(Group) %>%
mutate(Half = ifelse(row_number() <= ceiling(n()/2), "First", "Second")) %>%
ungroup() %>%
group_by(Group, Half) %>%
mutate(`Running Total` = cumsum(Value)) %>%
ungroup() %>%
select(-Half)
&&&