Generate result table on the basis of 2 problem tables. Sort is on Group and Company. Try making use of record functions as much as possible to solve this Power Query problem. This is not a complex problem but I want to encourage those people who have not been using record functions to apply record functions wherever possible.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 137
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Map Groups to Company with Power Query
Power Query solution 1 for Map Groups to Company, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Ans = Table.ExpandTableColumn(
Table.TransformColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{
"Company",
each Table.Join(
Table.FromRows(
List.Transform(
Text.SplitAny(_, ";,"),
each List.Transform(Text.Split(_, ":"), each try Number.From(_) otherwise Text.Trim(_))
),
{"ID", "Company"}
),
"Company",
Source,
"Company"
)
}
),
"Company",
{"ID", "Company", "Price"}
)
in
AnsPower Query solution 2 for Map Groups to Company, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T1 = Source("Table1"),
T2 = Source("Table2"),
H = Table.ColumnNames,
S = Table.Sort(
Table.FromRows(
List.TransformMany(
Table.ToRows(T1),
(i) =>
List.Split(
List.Transform(Splitter.SplitTextByAnyDelimiter({",", ":", ";"}, 1)(i{1}), Text.Trim),
2
),
(i, o) => {i{0}} & o & {T2{[Company = o{1}]}[Price]}
),
{H(T1){0}, "ID"} & H(T2)
),
H(T1)
)
in
SPower Query solution 3 for Map Groups to Company, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T1 = Source("Table1"),
S = Table.Sort(
Table.FromRecords(
List.Combine(
Table.AddColumn(
T1,
"Record",
(r) =>
List.Transform(
List.Split(
Splitter.SplitTextByAnyDelimiter({",", ":", ";"}, 1)(Record.Field(r, "Company")),
2
),
(m) =>
let
t = Record.TransformFields(
Record.FromList(m, {"ID", "Company"}),
{{"ID", Text.Trim}, {"Company", Text.Trim}}
)
in
Record.RemoveFields(r, {"Company"})
& t
& Record.RemoveFields(
Source("Table2"){[Company = Record.Field(t, "Company")]},
"Company"
)
)
)[Record]
)
),
Table.ColumnNames(T1)
)
in
SPower Query solution 4 for Map Groups to Company, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Expand = Table.ExpandListColumn(
Table.TransformColumns(Source, {{"Company", Splitter.SplitTextByAnyDelimiter({":", ",", ";"})}}),
"Company"
),
Trim = Table.TransformColumns(
Expand,
{
"Company",
each
let
a = Text.Trim(_),
b = try Number.From(a) otherwise a
in
b
}
),
Idx = Table.AddIndexColumn(Trim, "Index", 1, 0.5),
Class = Table.AddColumn(
Idx,
"Custom",
each if Number.Mod([Index], 1) = 0 then "ID" else "Company"
),
IdxRound = Table.TransformColumns(Class, {"Index", each Number.RoundDown(_)}),
Pivot = Table.Pivot(IdxRound, List.Distinct(IdxRound[Custom]), "Custom", "Company"),
Remove = Table.RemoveColumns(Pivot, {"Index"}),
Sort = Table.Sort(Remove, {{"Group", Order.Ascending}, {"Company", Order.Ascending}}),
Merge = Table.NestedJoin(Sort, {"Company"}, Table2, {"Company"}, "Table2", JoinKind.LeftOuter),
Xpand = Table.ExpandTableColumn(Merge, "Table2", {"Price"}, {"Price"})
in
XpandPower Query solution 5 for Map Groups to Company, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Table.TransformColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{
"Company",
each Table.Combine(
List.Transform(
Text.SplitAny(Text.Remove(_, " "), ";,"),
each Table.FromRows({Text.Split(_, ":")}, {"ID", "Company"})
)
)
}
),
Xpand = Table.ExpandTableColumn(Source, "Company", {"ID", "Company"}),
Sol = Table.Sort(
Table.AddColumn(
Xpand,
"Custom",
(x) => Table.SelectRows(Table2, each [Company] = x[Company])[Price]{0}
),
{{"Group", 0}, {"Company", 0}}
)
in
SolPower Query solution 6 for Map Groups to Company, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Process = List.Transform(
Table.ToRecords(Source),
each Record.TransformFields(
_,
{
"Company",
each
let
a = Table.FromRows(
List.Transform(Text.SplitAny(Text.Remove(_, " "), ";,"), each Text.Split(_, ":")),
{"ID", "Company"}
),
b = Table.AddColumn(
a,
"Price",
(x) => List.Select(Table.ToRows(Table2), each List.Contains(_, x[Company])){0}{1}
)
in
b
}
)
),
Groups = Table.Combine(List.Transform(Process, each Table.FromRecords({_}))),
Sol = Table.Sort(
Table.ExpandTableColumn(Groups, "Company", Table.ColumnNames(Groups[Company]{0})),
{{"Group", 0}, {"Company", 0}}
)
in
SolPower Query solution 7 for Map Groups to Company, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
trf = Table.TransformColumns(
Fonte,
{
"Company",
each [
a = List.Combine(
List.TransformMany(
{_},
(o) => Text.Split(Text.Remove(o, " "), ";"),
(x, y) => Text.Split(y, ",")
)
),
b = Table.Sort(
Table.FromRows(List.Transform(a, (p) => Text.Split(p, ":")), {"ID", "Company"}),
{each [Company], 0}
),
c = Table.AddColumn(
b,
"Price",
each Table.SelectRows(Tabela2, (x) => [Company] = x[Company])[Price]{0}
)
][c]
}
),
res = Table.ExpandTableColumn(trf, "Company", Table.ColumnNames(trf[Company]{0}))
in
resPower Query solution 8 for Map Groups to Company, proposed by Alexis Olson:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Split = Table.TransformColumns(
T1,
{{"Company", each Text.SplitAny(Text.Remove(_, " "), ";,"), type list}}
),
ExpandToRows = Table.ExpandListColumn(Split, "Company"),
ToRecord = Table.TransformColumns(
ExpandToRows,
{{"Company", each [ID = Text.BeforeDelimiter(_, ":"), Company = Text.AfterDelimiter(_, ":")]}}
),
ExpandToCols = Table.ExpandRecordColumn(ToRecord, "Company", {"ID", "Company"}),
AddPrice = Table.AddColumn(ExpandToCols, "Price", each T2{[Company = [Company]]}[Price])
in
AddPricePower Query solution 9 for Map Groups to Company, proposed by Ramiro Ayala Chávez:
let
t1 = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Tabla2"]}[Content],
a = Table.ReplaceValue(t1, ",", ";", Replacer.ReplaceText, {"Company"}),
b = Table.ExpandListColumn(
Table.TransformColumns(a, {{"Company", Splitter.SplitTextByDelimiter(";")}}),
"Company"
),
c = Table.SplitColumn(b, "Company", Splitter.SplitTextByDelimiter(":"), {"ID", "Company"}),
d = Table.TransformColumns(c, {{"ID", each Text.Trim(_)}, {"Company", each Text.Trim(_)}}),
e = Table.AddColumn(d, "Price", each t2[Price]{List.PositionOf(t2[Company], [Company])}),
f = Table.Group(e, {"Group"}, {{"G", each Table.Sort(_, {{"Company", 0}})}})[[G]],
Sol = Table.Combine(f[G])
in
SolPower Query solution 10 for Map Groups to Company, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S2 = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content],
S1 = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content],
A1 = Table.AddColumn(
S1,
"C",
each Splitter.SplitTextByAnyDelimiter({":", ",", ";"})(Text.Remove([Company], " "))
),
A2 = Table.AddColumn(
A1,
"C1",
each Table.FromColumns(
{List.Alternate([C], 1, 1, 1), List.Alternate([C], 1, 1)},
{"ID", "Company"}
)
),
R = Table.SelectColumns(A2, {"Group", "C1"}),
E = Table.ExpandTableColumn(R, "C1", {"Company", "ID"}, {"Company", "ID"}),
A3 = Table.AddColumn(E, "C1", each Table.SelectRows(S2, (Inter) => Inter[Company] = [Company])),
E2 = Table.ExpandTableColumn(A3, "C1", {"Price"}, {"Price"}),
Re = Table.ReorderColumns(E2, {"Group", "ID", "Company", "Price"}),
S = Table.Sort(Re, {{"Group", Order.Ascending}, {"Company", Order.Ascending}})
in
SPower Query solution 11 for Map Groups to Company, proposed by Rafael González B.:
let
Source= Excel.Workbook(File.Contents("FileRoute"), null, true),
Groups_Table = Source{[Item="Groups",Kind="Table"]}[Data],
Prices_Table = Source{[Item="Prices",Kind="Table"]}[Data],
TTR = Table.ToRecords(Groups_Table),
RT = List.Transform(TTR, each
Record.TransformFields(_, {"Company", (x) =>
let
aa = Text.Remove(x, {" "}),
a = Text.SplitAny(aa, ",;"),
b = List.Transform(a, each Text.Split(_, ":")),
c = Table.FromRows(b, {"ID", "Company1"}),
d = Table.Join(c, "Company1", Prices_Table, "Company", 1)[[ID], [Company], [Price]],
e = Record.Field(_, "Group"),
f = Table.AddColumn(d, "Group", each e)
in
f}
)[Company]
)
in
Table.Combine(RT, {"Group", "ID", "Company", "Price"})
🧙♂️🧙♂️🧙♂️
Power Query solution 12 for Map Groups to Company, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.TransformColumns(
Source,
{{"Company", each Text.SplitAny(Text.Remove(_, " "), ";,"), type list}}
),
ExpandColumn = Table.ExpandListColumn(Split, "Company"),
ToRecord = Table.TransformColumns(
ExpandColumn,
{{"Company", each [ID = Text.BeforeDelimiter(_, ":"), Company = Text.AfterDelimiter(_, ":")]}}
),
ExpandedCompany = Table.ExpandRecordColumn(ToRecord, "Company", {"ID", "Company"}),
MergedQueries = Table.NestedJoin(
ExpandedCompany,
{"Company"},
Table2,
{"Company"},
"Table2",
JoinKind.LeftOuter
),
ExpandedTable2 = Table.ExpandTableColumn(MergedQueries, "Table2", {"Price"}, {"Price"}),
Sorted = Table.Sort(ExpandedTable2, {{"Group", Order.Ascending}})
in
SortedPower Query solution 13 for Map Groups to Company, proposed by Kerwin Tan CPA:
let
Table1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Transformed = Table.Group(
Table1,
{"Group"},
{
{
"Table",
each
let
src = Text.SplitAny(Text.Replace([Company]{0}, " ", ""), ";,"),
lookupList = List.Buffer(Table2[Company]),
price = List.Buffer(Table2[Price])
in
List.Transform(
src,
each _
& ":"
& Text.From(price{List.PositionOf(lookupList, Text.AfterDelimiter(_, ":"))})
)
}
}
),
Output = Table.SplitColumn(
Table.ExpandListColumn(Transformed, "Table"),
"Table",
Splitter.SplitTextByDelimiter(":"),
{"ID", "Company", "Price"}
)
in
OutputSolving the challenge of Map Groups to Company with Excel
Excel solution 1 for Map Groups to Company, proposed by Bo Rydobon 🇹🇭:
=REDUCE(E1:H1,A2:A5,LAMBDA(a,g,LET(c,SORT(TEXTSPLIT(VLOOKUP(g,A2:B5,2,0),{":"," "},{";",","},1),2),
VSTACK(a,IFNA(HSTACK(g,IFERROR(--c,c),VLOOKUP(DROP(c,,1),A10:B21,2,)),g)))))Excel solution 2 for Map Groups to Company, proposed by محمد حلمي:
=REDUCE(E1:H1,A2:A5,LAMBDA(a,b,LET(
e,TEXTSPLIT(TAKE(b:B2,-1,-1),{":",": "},{", ","; "}),
VSTACK(a,SORT(IFNA(HSTACK(b,IFERROR(--e,e),
VLOOKUP(DROP(e,,1),A9:B21,2,)),b),2,-1)))))Excel solution 3 for Map Groups to Company, proposed by 🇰🇷 Taeyong Shin:
=LET(s,TEXTSPLIT(TEXTJOIN(";",,B2:B5),{":",": "},{";",", ","; "}),c,DROP(s,,1),SORT(HSTACK(XLOOKUP("*"&c&"*",B2:B5,A2:A5,,2),IFERROR(--s,s),VLOOKUP(c,A10:B21,2,)),{1,3}))Excel solution 4 for Map Groups to Company, proposed by Kris Jaganah:
=LET(a,A2:A5,b,B2:B5,c,A10:A21,d,B10:B21,e,REDUCE({"ID","Company"},b,LAMBDA(x,y,VSTACK(x,TRIM(TEXTSPLIT(y,":",{",",";"}))))),f,TAKE(e,,-1),g,IFERROR(MAP(f,LAMBDA(x,FILTER(a,IFERROR(FIND(x,b),0)))),"Group"),h,HSTACK(g,e,XLOOKUP(f,c,d,"Price")),VSTACK(TAKE(h,1),SORT(DROP(h,1),{1,3},{1,1})))Excel solution 5 for Map Groups to Company, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=LET(k,A2:A5,l,IFNA(TEXTSPLIT(TEXTJOIN("|",,B2:B5),{",",";"},"|",TRUE),""),o, VSTACK(k&":"&INDEX(l,,1),k&":"&INDEX(l,,2),k&":"&INDEX(l,,3)),p, FILTER(o, LEN(o)>3),q, SUBSTITUTE(p," ",""),r, TEXTSPLIT(TEXTJOIN("|",,q),":","|",TRUE),u, XLOOKUP(INDEX(r,,3),A10:A21,B10:B21,,0),v, HSTACK(r, u),SORT(SORT(v,3),1))Excel solution 6 for Map Groups to Company, proposed by Oscar Mendez Roca Farell:
=LET(_c, B2:B5,_m, TRIM(TEXTSPLIT(CONCAT(_c&";"),":",{",",";"},1)),_g, TOCOL(IFS(ISNUMBER(FIND(TAKE(_m, ,1), TOROW(_c))), TOROW(A2:A5)), 2),_p, VLOOKUP(DROP(_m, ,1), A10:B21, 2, ) ,SORT(HSTACK(_g,_m,_p), {1, 3}))Excel solution 7 for Map Groups to Company, proposed by Duy Tùng:
=LET(a,REDUCE({"Group","ID","Company"},B2:B5,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(@+A5:y,SORT(TRIM(TEXTSPLIT(y,":",{";",","})),2)),@+A5:y)))),HSTACK(IFERROR(--a,a),VLOOKUP(TAKE(a,,-1),A9:B21,2,)))Excel solution 8 for Map Groups to Company, proposed by Sunny Baggu:
=LET(
_n, MAP(B2:B5, LAMBDA(x, ROWS(UNIQUE(TOCOL(SEARCH(":", x, SEQUENCE(30)), 3))))),
_g, DROP(TEXTSPLIT(CONCAT(REPT(A2:A5 & ",", _n)), , ","), -1),
_c, TEXTSPLIT(ARRAYTOTEXT&(B2:B5), {" : ", ":", " :", ": "}, {",", ";"}),
_p, XLOOKUP(TAKE(_c, , -1), A10:A21, B10:B21),
SORT(HSTACK(_g, _c, _p), {1, 3}, {1, 1})
)Excel solution 9 for Map Groups to Company, proposed by Asheesh Pahwa:
=DROP(LET(alp, A2:A5, REDUCE("",SEQUENCE(ROWS(B2:B5)), LAMBDA(x,y, VSTACK(x, LET(a, TEXTSPLIT(INDEX(B2:B5,y),{":"," : ",": "},{"; ",", "),s,SORTBY(a, TAKE(a,,-1),1),c,TAKE(s,, 1)&"-"&INDEX(alp,y,), b,XLOOKUP(TAKE(s,,-1), A10:A21,B10:B21),HSTACK(TEXTAFTER(c,"-"),s,b)))))),1)Excel solution 10 for Map Groups to Company, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(j;MAP(B2:B5;LAMBDA(y;TEXTJOIN(";";;MAP(TEXTSPLIT(y;;{";";","});LAMBDA(x;TEXTAFTER(x;":"))))));p;MAP(B2:B5;LAMBDA(m;TEXTJOIN(";";;SORT(TRIM(TEXTAFTER(TEXTSPLIT(m;;{";";","});":"));;1))));LET(z;TRIM(TEXTSPLIT(TEXTJOIN(";";;p);;";"));HSTACK(BYROW(TRIM(TEXTSPLIT(TEXTJOIN(";";;j);;";"));LAMBDA(z;FILTER(A2:A5;ISNUMBER(SEARCH(z;B2:B5;1)))));BYROW(z;LAMBDA(i;XLOOKUP(i;TRIM(TEXTSPLIT(TEXTJOIN(";";;j);;";"));TRIM(TEXTSPLIT(TEXTJOIN(";";;MAP(B2:B5;LAMBDA(y;TEXTJOIN(";";;MAP(TEXTSPLIT(y;;{";";","});LAMBDA(x;TEXTBEFORE(x;":")))))));;";")))));z;MAP(z;LAMBDA(s;XLOOKUP(s;A10:A21;B10:B21))))))Solving the challenge of Map Groups to Company with Python
Python solution 1 for Map Groups to Company, proposed by Luke Jarych:
Python xlwings + pandas:
import pandas as pd
import xlwings as xw
# Read Excel workbook
wb = xw.Book(r'C:UsersLukeDownloadsRecords-filtering.xlsx')
sh = wb.sheets[0]
# Extract data from tables
df1 = sh.tables['Table1'].range.options(pd.DataFrame, header=True, index=False).value
df2 = sh.tables['Table2'].range.options(pd.DataFrame, header=True, index=False).value
# Clean and process data
df1['Company'] = df1['Company'].astype(str).str.replace('[ ]', '', regex=True)
df_expanded = df1['Company'].str.split('[,;]', expand=False).explode().str.split(':', expand=True)
df_expanded.columns = ['ID', 'Company']
result_df1 = pd.merge(df1.drop(columns=['Company']), df_expanded, left_index=True, right_index=True, how='left')
# Merge dataframes
df2['Price'] = df2['Price'].astype(int)
# Display result
solution
Solving the challenge of Map Groups to Company with Python in Excel
Python in Excel solution 1 for Map Groups to Company, proposed by Alejandro Campos:
df_a=xl("A1:B5",headers=True)
df_b=xl("A9:B21",headers=True)
s=lambda r:[e.strip() for e in (r.split(';') if ';' in r else r.split(','))]
d=df_a.explode('Company');d['Company']=d['Company'].apply(s);d=d.explode('Company')
d[['ID','Company']]=d['Company'].str.split(':',expand=True)
d['ID'],d['Company']=d['ID'].str.strip(),d['Company'].str.strip()
r=pd.merge(d,df_b,on='Company',how='left')[['Group','ID','Company','Price']]
.sort_values(['Group','Company'])
.set_index('Group').reset_index()
Solving the challenge of Map Groups to Company with R
R solution 1 for Map Groups to Company, proposed by Konrad Gryczan, PhD:
Finally woke up :D
library(tidyverse)
library(readxl)
T1 = read_excel("PQ_Challenge_137.xlsx", range = "A1:B5")
T2 = read_excel("PQ_Challenge_137.xlsx", range = "A9:B21")
test = read_excel("PQ_Challenge_137.xlsx", range = "E1:H9")
T1_1 = T1 %>%
separate_rows(Company, sep = ";|,") %>%
mutate(Company = str_remove_all(Company, "[:space:]")) %>%
separate(Company, into = c("ID","Company"),sep = ":") %>%
mutate(ID = as.numeric(ID))
result = T1_1 %>%
left_join(T2, by = "Company") %>%
arrange(Group, Company)
&&