Generate the result table as shown. In case of more than one person in first column, amount will be divided equally among all persons in that row. Query has to be dynamic to accommodate future months, orders nos, names. No new year will come, so no need to worry about year. Sorting of Months and Name is important.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 4
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Divide values among people listed with Power Query
Power Query solution 1 for Divide values among people listed, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
SplitTrim = Table.TransformColumns(
Source,
{{"Name", each List.Transform(Text.Split(_, "/"), Text.Trim)}}
),
AAm = Table.AddColumn(SplitTrim, "Am", each [Amount] / List.Count([Name])),
EName = Table.ExpandListColumn(AAm, "Name"),
Grouped = Table.Group(
EName,
"Name",
{
{"Orders", each Text.Combine(List.Transform(List.Sort([#"Order No."]), Text.From), ",")},
{"A", each _}
}
),
ExpandedA = Table.ExpandTableColumn(Grouped, "A", {"Date", "Am"}, {"Date", "Am"}),
AMonth = Table.TransformColumns(
Table.Sort(ExpandedA, {{"Date", Order.Ascending}}),
{{"Date", each DateTime.ToText(_, "MMM")}}
),
Pivoted = Table.Pivot(
AMonth,
List.Distinct(AMonth[Date]),
"Date",
"Am",
each Number.Round(List.Sum(_), 2)
),
ATotal = Table.AddColumn(Pivoted, "Total", each List.Sum(List.RemoveFirstN(Record.ToList(_), 2)))
in
ATotalPower Query solution 2 for Divide values among people listed, proposed by 🇰🇷 Taeyong Shin:
let
Source = Excel.CurrentWorkbook(){[Name = "tblData"]}[Content],
ReplaceValue = Table.ReplaceValue(Source, " ", "", Replacer.ReplaceText, {"Name"}),
Splitter = Table.ExpandListColumn(
Table.TransformColumns(
ReplaceValue,
{
{"Name", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), type text},
{"Date", each Date.ToText(Date.From(_), "MMM"), type text},
{"Order No.", Text.From}
}
),
"Name"
),
AddDiv = Table.AddColumn(
Splitter,
"Div",
each [
Lst = List.Select(Splitter[#"Order No."], (x) => x = [#"Order No."]),
Divide = Number.Round([Amount] / List.Count(Lst), 2)
][Divide],
type number
),
Group = Table.Group(
AddDiv,
"Name",
{
{"Order Nos.", each Text.Combine([#"Order No."], ","), type text},
{"All", each _[[Date], [Div]], type table [Date = text, Div = number]}
}
),
Expand = Table.ExpandTableColumn(Group, "All", {"Date", "Div"}, {"Date", "Div"}),
Pivot = Table.Pivot(
Expand,
List.Sort(List.Distinct(Expand[Date]), Order.Descending),
"Date",
"Div",
List.Sum
),
AddTotal = Table.AddColumn(
Pivot,
"Total",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
)
in
AddTotalPower Query solution 3 for Divide values among people listed, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
Part3:
#"Expanded All1" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"Date", "FinalAmount"}, {"Date", "FinalAmount"}),
#"Sorted Rows" = Table.Sort(#"Expanded All1",{{"Name", Order.Ascending}, {"Date", Order.Descending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Date]), "Date", "FinalAmount", List.Sum),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Name", "Order Nos.", "June", "July", "May", "Total Amount"})
in
#"Reordered Columns"
Power Query solution 4 for Divide values among people listed, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
Part2:
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Order No.", "Value", "Date", "Amount"}, {"Order No..1", "Value", "Date", "Amount"}),
#"Added Custom" = Table.AddColumn(#"Expanded All", "FinalAmount", each [Amount]/[Count]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Value", "Name"}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns",{"Order No.", "Name", "Date", "FinalAmount"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns1",{{"Name", Text.Trim, type text}}),
#"Rounded Off" = Table.TransformColumns(#"Trimmed Text",{{"FinalAmount", each Number.Round(_, 2), type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Rounded Off",{{"Order No.", type text}}),
#"Grouped Rows1" = Table.Group(#"Changed Type2", {"Name"}, {{"Total Amount", each List.Sum([FinalAmount]), type number}, {"Order Nos.", each Text.Combine([#"Order No."],","), type nullable text}, {"All", each _, type table [#"Order No."=nullable text, Name=text, Date=nullable text, FinalAmount=number]}}),
Power Query solution 5 for Divide values among people listed, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No.", Int64.Type}, {"Name", type text}, {"Date", type datetime}, {"Amount", type number}}),
#"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Date", each Date.MonthName(_), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Month Name", "Name", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}, {"Name.3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Order No.", "Date", "Amount"}, "Attribute", "Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Order No.", "Value", "Date", "Amount"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Order No."}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [#"Order No."=nullable number, Value=text, Date=text, Amount=nullable number]}}),
Solving the challenge of Divide values among people listed with Excel
Excel solution 1 for Divide values among people listed, proposed by محمد حلمي:
=LET(
A,
HSTACK(
TEXTSPLIT(
C3:C15,
"/"
),
TRIM(
TEXTSPLIT(
TEXTAFTER(
C3:C15,
"/",
{1,
2}
),
"/"
)
)
),
R,
LAMBDA(Z,
TEXTSPLIT(TRIM(CONCAT(REPT(Z&" ",
BYROW(IFNA(
A,
),
LAMBDA(X,
SUM(--(LEN(
X
)>1)))))&" ")),
,
" ")),
AA,
E3:E15/(LEN(
C3:C15
)-LEN(
SUBSTITUTE(
C3:C15,
"/",
""
)
)+1),
V,
R(
B3:B15
),
X,
TRIM(
TOCOL(
A,
2
)
),
W,
SORT(
UNIQUE(
X
)
),
O,
R(
D3:D15
)+0,
N,
R(
AA
)+0,
M,
LAMBDA(I,
MAP(W,
LAMBDA(W,
SUM( IF((W=X)*(MONTH(
O
)=I),
N))))),
L,
VSTACK(
HSTACK(
{"Name",
"Order Nos."},
TOROW(
SORT(
UNIQUE(
TEXT(
D3:D15,
"MMM"
)
),
,
-1
)
),
"Total"
),
HSTACK(
W,
MAP(
W,
LAMBDA(
W,
TEXTJOIN(
", ",
,
IF(
W=X,
V,
""
)
)
)
),
M(
5
),
M(
6
),
M(
7
),
M(
5
)+M(
6
)+M(
7
)
)
),
IF(
L=0,
"",
L
))Excel solution 2 for Divide values among people listed, proposed by 🇰🇷 Taeyong Shin:
=LET(
t,
TEXTSPLIT(
CONCAT(
B3:B15&","
),
{"/",
" "},
",",
1
),
F,
LAMBDA(
x,
TOCOL(
IF(
t>"",
x
),
2
)
),
o,
TOCOL(
t,
2
),
d,
C3:C15,
n,
LEN(
SUBSTITUTE(
B3:B15,
"/",
"1/"
)
)-LEN(
B3:B15
)+1,
CHOOSECOLS(
HSTACK(
DROP(
PIVOTBY(
o,
HSTACK(
F(
MONTH(
d
)
),
F(
TEXT(
d,
"mmm"
)
)
),
F(
D3:D15/n
),
LAMBDA(
x,
ROUND(
SUM(
x
),
2
)
),
,
0,
,
,
1
),
1
),
GROUPBY(
VSTACK(
B2,
o
),
VSTACK(
A2,
F(
A3:A15
)
),
ARRAYTOTEXT,
3,
0
)
),
-2,
-1,
2,
3,
4,
5
)
)Excel solution 3 for Divide values among people listed, proposed by Duy Tùng:
=LET(
a,
TRIM(
TEXTSPLIT(
ARRAYTOTEXT(
C3:C15
),
"/",
", "
)
),
f,
LAMBDA(
v,
TOCOL(
IFS(
a>0,
v
),
3
)
),
HSTACK(
VSTACK(
{"Name",
"Order No"},
GROUPBY(
f(
a
),
f(
B3:B15
),
ARRAYTOTEXT,
,
0
)
),
DROP(
PIVOTBY(
f(
a
),
TEXT(
f(
D3:D15
),
"mmm"
),
f(
E3:E15/BYROW(
N(
ISTEXT(
a
)
),
SUM
)
),
SUM,
,
0,
,
1
),
,
1
)
)
)Excel solution 4 for Divide values among people listed, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=TEXTJOIN(
", ";
;
IFERROR(
INDEX(
A:A;
IF(
FIND(
$H2;
$B$2:$B$14;
1
)>=1;
ROW(
$A$2:$A$100
);
""
)
);
""
)
)Excel solution 5 for Divide values among people listed, proposed by Sergei Baklan:
=LET(
dataName, CHOOSECOLS( data, 2),
dates, CHOOSECOLS( data, 3),
amount, CHOOSECOLS( data, 4),
names, SORT( UNIQUE( TOCOL( TRIM( TEXTSPLIT( TEXTJOIN(";",, dataName), "/", ";") ), 3) ) ),
months, TOROW( UNIQUE( TEXT( SORTBY( dates, MONTH(dates) ), "mmm") ) ),
nRows, ROWS( names ),
nColumns, COLUMNS( months ),
orders, MAP( names, LAMBDA(v, TEXTJOIN(",", , IF( ISNUMBER( SEARCH( v, dataName ) ), CHOOSECOLS( data, 1 ), "") ) ) ),
personAmount, amount / ( LEN( dataName ) - LEN( SUBSTITUTE( dataName, "/", "") ) +1 ),
grid, MAKEARRAY( nRows, nColumns, LAMBDA(n,m,
SUM( personAmount *
( TEXT( dates, "mmm" ) = INDEX( months, m ) ) *
ISNUMBER( SEARCH( INDEX(names, n ), dataName ) ) ) ) ),
totals, BYROW(grid, LAMBDA(rw, SUM(rw) ) ),
VSTACK(
HSTACK( "Name", "Order Nos.", months, "Total" ),
HSTACK( names, orders, grid, totals)
) )