Merger the tables into a single table. Where Org and Year are same, sum the Sales. Sort on Org and Year.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 478
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Merge Tables by Org and Year with Power Query
Power Query solution 1 for Merge Tables by Org and Year, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(),
Filter = Table.SelectRows(Source, each [Name] <> "Report"),
Combine = Table.Combine(Filter[Content]),
Group = Table.Group(
Combine,
{"Org", "Year"},
{{"Prime", each List.Min([Prime])}, {"Sales", each List.Sum([Sales])}}
),
Return = Table.Sort(Group, {"Org", "Year"})
in
ReturnPower Query solution 2 for Merge Tables by Org and Year, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Tbl1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Group = Table.Group(
Tbl2 & Tbl1,
{"Org", "Year"},
{
{
"A",
each
let
a = Table.FromColumns(
{{Text.Combine([Prime])}, {List.Sum([Sales])}},
{"Prime", "Sales"}
)
in
a
}
}
),
Sol = Table.Sort(
Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0})),
{"Org", "Year"}
)
in
SolPower Query solution 3 for Merge Tables by Org and Year, proposed by Luan Rodrigues:
let
Fonte = Table.Combine(Excel.CurrentWorkbook()[Content]),
gp = Table.Group(
Fonte,
{"Org", "Year"},
{{"Prime", each List.RemoveNulls([Prime]){0}? ?? null}, {"Sales", each List.Sum([Sales])}}
),
res = Table.Sort(gp, {{"Org", 0}, {"Year", 0}})
in
resPower Query solution 4 for Merge Tables by Org and Year, proposed by Ramiro Ayala Chávez:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
A = Table.AddColumn,
a = Table.Group(t2 & t1, {"Org", "Year"}, {"Sales", each List.Sum([Sales])}),
b = A(a, "M", each [Org] & Text.From([Year])),
c = A(t2, "M", each [Org] & Text.From([Year])),
d = A(b, "Prime", each try c[Prime]{List.PositionOf(c[M], [M])} otherwise null),
Sol = Table.Sort(Table.SelectColumns(d, Table.ColumnNames(t2)), {{"Org", 0}, {"Year", 0}})
in
SolPower Query solution 5 for Merge Tables by Org and Year, proposed by Rafael González B.:
let
T1 = Excel.CurrentWorkbook(){0}[Content],
T2 = Excel.CurrentWorkbook(){1}[Content],
TJ = T1 & T2,
GB = Table.Group(TJ,
{"Org", "Year"},
{
{"Sales", each List.Sum([Sales])},
{"Prime", each Table.SelectRows(_[[Prime]], each [Prime] <> null )}
}),
EXP = Table.ExpandTableColumn(GB, "Prime", {"Prime"}, {"Prime"}),
Result = Table.Sort(EXP,{{"Org",0}, {"Year",0}})[[Org], [Year], [Prime], [Sales]]
in
Result
🧙♂️🧙♂️🧙♂️
Solving the challenge of Merge Tables by Org and Year with Excel
Excel solution 1 for Merge Tables by Org and Year, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A3:C9,
b,
E2:H10,
DROP(
GROUPBY(
VSTACK(
TAKE(
b,
,
2
),
TAKE(
a,
,
2
)
),
VSTACK(
DROP(
b,
,
2
),
IF(
{1,
0},
"",
DROP(
a,
,
2
)
)
),
HSTACK(
LAMBDA(
x,
T(
@x
)
),
SUM
),
3,
0
),
1
)
)Excel solution 2 for Merge Tables by Org and Year, proposed by John V.:
=GROUPBY(VSTACK(HSTACK(A3:B9,XLOOKUP(A3:A9&B3:B9,E3:E10&F3:F10,G3:G10,"")),E3:G10)&"",VSTACK(C3:C9,H3:H10),SUM,,0)Excel solution 3 for Merge Tables by Org and Year, proposed by محمد حلمي:
=LET(
i,
SORT(
UNIQUE(
VSTACK(
A3:B9,
E3:F10
)
)
),
e,
TAKE(
i,
,
1
)&DROP(
i,
,
1
),
x,
E3:E10&F3:F10,
HSTACK(
i,
XLOOKUP(
e,
x,
G3:G10&"",
""
),
XLOOKUP(
e,
A3:A9&B3:B9,
C3:C9,
0
)+XLOOKUP(
e,
x,
H3:H10,
0
)
)
)Excel solution 4 for Merge Tables by Org and Year, proposed by 🇰🇷 Taeyong Shin:
=LET(
f,
LAMBDA(
x,
CHOOSECOLS(
x,
{1,
2,
4,
3}
)
),
d,
VSTACK(
f(
E2:H10
),
EXPAND(
A3:C9,
,
4,
""
)
),
f(
DROP(
GROUPBY(
TAKE(
d,
,
2
),
DROP(
d,
,
2
),
HSTACK(
SUM,
LAMBDA(
x,
T(
@x
)
)
),
3,
0
),
1
)
)
)Excel solution 5 for Merge Tables by Org and Year, proposed by Kris Jaganah:
=LET(
a,
E2:H10,
b,
A2:C9,
c,
VSTACK(
a,
IFNA(
DROP(
REDUCE(
"",
TAKE(
a,
1
),
LAMBDA(
x,
y,
HSTACK(
x,
FILTER(
DROP(
b,
1
),
TAKE(
b,
1
)=y,
""
)
)
)
),
,
1
),
)
),
d,
IF(
c=0,
"",
c
),
DROP(
GROUPBY(
TAKE(
d,
,
2
),
DROP(
d,
,
2
),
HSTACK(
CONCAT,
SUM
),
3,
0
),
1
)
)Excel solution 6 for Merge Tables by Org and Year, proposed by Julian Poeltl:
=LET(
T;
A3:C9;
TT;
E2:H10;
TTT;
DROP(
TT;
1
);
TTTO;
TAKE(
TTT;
;
1
);
TTTT;
CHOOSECOLS(
TTT;
2
);
TO;
TAKE(
T;
;
1
);
TTw;
CHOOSECOLS(
T;
2
);
X;
XLOOKUP(
TTTO&TTTT;
TO&TTw;
TAKE(
T;
;
-1
)
);
A;
HSTACK(
TAKE(
TTT;
;
3
);
TAKE(
TTT;
;
-1
)+IFNA(
X;
0
)
);
B;
FILTER(
T;
NOT(
ISNUMBER(
XMATCH(
TO&TTw;
TTTO&TTTT
)
)
)
);
AR;
VSTACK(
HSTACK(
EXPAND(
TAKE(
B;
;
2
);
;
3;
""
);
TAKE(
B;
;
-1
)
);
A
);
S;
SORT(
SORT(
AR;
2
);
1
);
VSTACK(
TAKE(
TT;
1
);
IF(
S="";
"";
S
)
)
)Excel solution 7 for Merge Tables by Org and Year, proposed by Julian Poeltl:
=LET(
T;
A3:C9;
TT;
E2:H10;
TTT;
DROP(
TT;
1
);
X;
XLOOKUP(
TAKE(
TTT;
;
1
)&CHOOSECOLS(
TTT;
2
);
TAKE(
T;
;
1
)&CHOOSECOLS(
T;
2
);
TAKE(
T;
;
-1
)
);
A;
HSTACK(
TAKE(
TTT;
;
3
);
TAKE(
TTT;
;
-1
)+IFNA(
X;
0
)
);
B;
FILTER(
T;
NOT(
ISNUMBER(
XMATCH(
TAKE(
T;
;
1
)&CHOOSECOLS(
T;
2
);
TAKE(
TTT;
;
1
)&CHOOSECOLS(
TTT;
2
)
)
)
)
);
AR;
VSTACK(
HSTACK(
EXPAND(
TAKE(
B;
;
2
);
;
3;
""
);
TAKE(
B;
;
-1
)
);
A
);
S;
SORT(
SORT(
AR;
2
);
1
);
VSTACK(
TAKE(
TT;
1
);
IF(
S="";
"";
S
)
)
)Excel solution 8 for Merge Tables by Org and Year, proposed by Timothée BLIOT:
=LET(A,
A3:A9,
B,
E3:E10,
C,
B3:B9,
D,
F3:F10,
E,
C3:C9,
F,
H3:H10,
G,
G3:G10,
H,
HSTACK,
V,
VSTACK,
M,
GROUPBY(
H(
V(
A,
B
),
V(
C,
D
)
),
V(
E,
F
),
SUM,
,
0
),
H(TAKE(
M,
,
2
),
MAP(TAKE(
M,
,
1
),
CHOOSECOLS(
M,
2
),
LAMBDA(x,
y,
IF(SUM(--(x=B)*--(y=D)*--(G="Yes")),
"Yes",
""))),
TAKE(
M,
,
-1
)))Excel solution 9 for Merge Tables by Org and Year, proposed by Oscar Mendez Roca Farell:
=LET(F,
LAMBDA(
i,
CHOOSECOLS(
VSTACK(
CHOOSECOLS(
EXPAND(
A3:C9,
,
4,
),
{1,
2,
4,
3}
),
E3:H10
),
i
)
),
u,
SORT(
UNIQUE(
F(
{1,
2}
)
)
),
HSTACK(u,
XLOOKUP(
TAKE(
u,
,
1
)&DROP(
u,
,
1
),
F(
1
)&F(
2
),
F(
3
)&"",
,
,
-1
),
BYROW(u,
LAMBDA(r,
SUM(F(
4
)*(F(
1
)&F(
2
)=CONCAT(
r
)))))))Excel solution 10 for Merge Tables by Org and Year, proposed by Duy Tùng:
=LET(
a,
A3:B9,
b,
E2:H10,
c,
VSTACK(
IF(
b>0,
b,
""
),
HSTACK(
a,
T(
XLOOKUP(
BYROW(
a,
CONCAT
),
BYROW(
E3:F10,
CONCAT
),
G3:G10,
""
)
),
C3:C9
)
),
GROUPBY(
TAKE(
c,
,
3
),
DROP(
c,
,
3
),
SUM,
3,
0
)
)Excel solution 11 for Merge Tables by Org and Year, proposed by LEONARD OCHEA 🇷🇴:
=LET(
m,
VSTACK(
E2:H10,
CHOOSECOLS(
A3:D9,
{1;2;4;3}
)
),
n,
IF(
m=0,
"",
m
),
DROP(
GROUPBY(
TAKE(
n,
,
2
),
TAKE(
n,
,
-2
),
HSTACK(
CONCAT,
SUM
),
3,
0
),
1
)
)Excel solution 12 for Merge Tables by Org and Year, proposed by 🇵🇪 Ned Navarrete C.:
=GROUPBY(VSTACK(HSTACK(A3:B9,XLOOKUP(A3:A9&B3:B9,E3:E10&F3:F10,G3:G10,0)),E3:G10),VSTACK(C3:C9,H3:H10),SUM,,0)Excel solution 13 for Merge Tables by Org and Year, proposed by Asheesh Pahwa:
=LET(
_t1,
A3:C9,
_t2,
CHOOSECOLS(
E3:H10,
{1,
2,
4}
),
vs,
VSTACK(
_t1,
_t2
),
ta,
TAKE(
vs,
,
1
),
ua,
UNIQUE(
ta
),
uy,
UNIQUE(
CHOOSECOLS(
vs,
2
)
),
r,
DROP(
REDUCE(
"",
ua,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
vs,
ta=y
),
c,
& UNIQUE(
INDEX(
f,
,
2
)
),
rr,
DROP(
REDUCE(
"",
c,
LAMBDA(
a,
v,
VSTACK(
a,
SUM(
FILTER(
CHOOSECOLS(
f,
3
),
CHOOSECOLS(
f,
2
)=v
)
)
)
)
),
1
),
IFNA(
HSTACK(
y,
c,
rr
),
y
)
)
)
)
),
1
),
x,
XLOOKUP(
INDEX(
r,
,
1
)&INDEX(
r,
,
2
),
E3:E10&F3:F10,
G3:G10,
""
),
HSTACK(
CHOOSECOLS(
r,
{1,
2}
),
IF(
x=0,
"",
x
),
TAKE(
r,
,
-1
)
)
)Excel solution 14 for Merge Tables by Org and Year, proposed by El Badlis Mohd Marzudin:
=LET(
a,
SORT(
VSTACK(
CHOOSE(
{1,
1,
2,
3},
A3:B9,
"",
C3:C9
),
IF(
E3:H10="",
"",
E3:H10
)
),
{1,
2,
3},
{1,
1,
-1}
),
d,
SORT(
DROP(
GROUPBY(
CHOOSECOLS(
a,
1,
2
),
TAKE(
a,
,
-1
),
SUM
),
-1
),
{1,
2}
),
e,
XLOOKUP(
BYROW(
TAKE(
d,
,
2
),
LAMBDA(
x,
CONCAT(
x
)
)
),
BYROW(
TAKE(
a,
,
2
),
LAMBDA(
x,
CONCAT(
x
)
)
),
CHOOSECOLS(
a,
3
)
),
HSTACK(
TAKE(
d,
,
2
),
e,
TAKE(
d,
,
-1
)
)
)Excel solution 15 for Merge Tables by Org and Year, proposed by Burhan Cesur:
=PIVOTBY(VSTACK(HSTACK(A3:B9,XLOOKUP(A3:A9&B3:B9,E3:E10&F3:F10,G3:G10,0)),E3:G10),,VSTACK(C3:C9,H3:H10),SUM,0,0)Solving the challenge of Merge Tables by Org and Year with Python
Python solution 1 for Merge Tables by Org and Year, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "478 Merge Tables.xlsx"
input1 = pd.read_excel(path, skiprows=1, usecols="A:C", nrows = 7)
input2 = pd.read_excel(path, skiprows=1, usecols="E:H", nrows = 8)
input2.columns = input2.columns.str.replace(r'.d+', '', regex=True)
test = pd.read_excel(path, skiprows=1, usecols="J:M")
test.columns = test.columns.str.replace(r'.d+', '', regex=True)
result = pd.merge(input1, input2, on=["Org", "Year"], how="outer").sort_values(by=["Org", "Year"])
result["Sales"] = result[["Sales_x", "Sales_y"]].sum(axis=1, skipna=True).astype("int64")
result = result[["Org", "Year", "Prime", "Sales"]].reset_index(drop=True)
print(result.equals(test)) # True
Python solution 2 for Merge Tables by Org and Year, proposed by Luan Rodrigues:
PY Solution
import pandas as pd
file = r"Excel_Challenge_478 - Merge Tables.xlsx"
df1 = pd.read_excel(file,usecols="A:C",skiprows=1)
df2 = pd.read_excel(file,usecols="E:H",skiprows=1)
cab = [i.replace('.1','') for i in df2.columns]
df2.columns = cab
df_concac = pd.concat([df2,df1]).reset_index(drop=True)
df_filtro = df_concac[df_concac['Org'].fillna('0')!= '0']
df_group = df_filtro.groupby(["Org", "Year"]).agg(
{'Prime': lambda x: next((i for i in x if pd.notna(i)), None),'Sales': 'sum'}).reset_index()
df_group = df_group.sort_values(by=['Org','Year'])
print(df_group)
Solving the challenge of Merge Tables by Org and Year with Python in Excel
Python in Excel solution 1 for Merge Tables by Org and Year, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_478 - Merge Tables.xlsx'
df1 = pd.read_excel(file_path, usecols='A:C', skiprows=1, nrows=7)
df2 = pd.read_excel(file_path, usecols='E:H', skiprows=1, nrows=8)
# Perform data wrangling
df2.columns = [x.replace('.1', '') for x in df2.columns]
df = pd.concat([df2, df1])
df['Sales'] = df.groupby(['Org', 'Year'])['Sales'].transform('sum')
df['Prime'] = df.groupby(['Org', 'Year'])['Prime'].transform('ffill')
df = df.drop_duplicates().sort_values(by=['Org', 'Year'], ignore_index=True)
df['Prime'] = df['Prime'].replace(float('nan'), '')
df
Solving the challenge of Merge Tables by Org and Year with R
R solution 1 for Merge Tables by Org and Year, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/478 Merge Tables.xlsx"
input1 = read_excel(path, range = "A2:C9")
input2 = read_excel(path, range = "E2:H10")
test = read_excel(path, range = "J2:M14")
result = input1 %>%
full_join(input2, by = c("Org", "Year")) %>%
arrange(Org, Year) %>%
mutate(Sales = map2_dbl(Sales.x, Sales.y, ~ sum(c(.x, .y), na.rm = TRUE))) %>%
select(Org, Year, Prime, Sales)
identical(result, test)
#> [1] TRUE
Solving the challenge of Merge Tables by Org and Year with DAX
DAX solution 1 for Merge Tables by Org and Year, proposed by Zoran Milokanović:
EVALUATE
GROUPBY(
UNION(SELECTCOLUMNS(Table1, Table1[Org], Table1[Year], "Prime", BLANK(), Table1[Sales]), Table2),
[Org], [Year],
"Prime", MAXX(CURRENTGROUP(), [Prime]),
"Sales", SUMX(CURRENTGROUP(), [Sales])
)
ORDER BY
[Org], [Year]
&&