Lookup the Approximate Milk Purity Dynamic array function allowed but Extra marks for Legacy Array Functions or PowerQuery Solution
📌 Challenge Details and Links
Challenge Number: 18
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Two-Way approximate Lookup with multiple criteria with Power Query
Power Query solution 1 for Two-Way approximate Lookup with multiple criteria, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = Table.SelectRows(Source, each [Milk] = "Premium Milk"),
Sort = Table.SelectColumns(
Rows,
List.FirstN(Table.ColumnNames(Source), 2)
& List.Sort(List.Skip(Table.ColumnNames(Source), 2), each Number.From(_))
),
Time = List.PositionOf(Sort[Time], List.Select(Sort[Time], each _ < 184){0}),
Temp = List.PositionOf(
Table.ColumnNames(Sort),
Text.From(
List.Last(
List.Select(
List.Transform(List.Skip(Table.ColumnNames(Sort), 2), Number.From),
each _ < 147
)
)
)
),
Sol = Number.ToText(Table.ToRows(Sort){Time}{Temp}, "P0")
in
SolPower Query solution 2 for Two-Way approximate Lookup with multiple criteria, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lookup = {"Premium Milk", "184", "147"},
UnpivotOther = Table.TransformColumnTypes(
Table.UnpivotOtherColumns(Source, {"Milk", "Time"}, "Temperature", "Purity"),
{"Temperature", Decimal.Type}
),
Group = Table.Group(
UnpivotOther,
{"Milk", "Time"},
{
{
"All",
each _,
type table [Milk = text, Time = number, Temperature = number, Purity = number]
}
}
),
AddTempRank = Table.RemoveColumns(
Table.AddColumn(
Group,
"TempRank",
each Table.AddRankColumn(
[All],
"TempRnk",
{"Temperature", Order.Ascending},
[RankKind = RankKind.Dense]
)
),
"All"
),
Expand = Table.ExpandTableColumn(
AddTempRank,
"TempRank",
{"Temperature", "Purity", "TempRnk"},
{"Temperature", "Purity", "TempRnk"}
),
Filter = List.Last(
Table.ToColumns(
Table.SelectRows(
Expand,
each ([Milk] = Lookup{0})
and ([Time] <= Number.From(Lookup{1}))
and ([Temperature] <= Number.From(Lookup{2}))
)
){3}
)
in
FilterPower Query solution 3 for Two-Way approximate Lookup with multiple criteria, proposed by Bhavya Gupta:
let
lookup = [Milk = "Premium Milk", Time = 184, Temp = 147],
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"dZE7DsIwEESvElmiSxGHrL2+ABVISBQUUQqKABGfSEHch7NwMuJZHAeRNNN43+zsuCzVrj10yaa5XlSq8px6NbTo1eVeKfNq2SvbharSUu3P7bVOVl39OA+g9SBpjC2BAHRQYwTcdvWted4GKMsGyFqowTg2m6VA6/Z+StbNsQb2fvVPGtsYoZzgo82GBfw5TOMwG+1ZFB62mD8MjXxziT3FXrzVxGGaslhdAXWxk9DGxDYt/QuSh+FQY4g51cjo42gUUIJT8d+IdOgwy5gtkJFN+PC5jEzxvyhWKQu5N6mqDw==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Milk = _t,
Time = _t,
#"37.5" = _t,
#"150.7" = _t,
#"70.3" = _t,
#"140.6" = _t,
#"96.6" = _t
]
),
UnPivot = Table.UnpivotOtherColumns(Source, {"Milk", "Time"}, "Temp", "Purity"),
CT = Table.TransformColumnTypes(
UnPivot,
{{"Temp", type number}, {"Purity", Percentage.Type}, {"Time", Int64.Type}}
),
Grouped = Table.Group(CT, {"Milk"}, {{"All", each _}}){[Milk = lookup[Milk]]}[All],
Output = Table.Sort(
Table.SelectRows(Grouped, each ([Time] <= lookup[Time]) and ([Temp] < lookup[Temp])),
{{"Time", 1}, {"Temp", 1}}
){0}[[Purity]]
in
OutputSolving the challenge of Two-Way approximate Lookup with multiple criteria with Excel
Excel solution 1 for Two-Way approximate Lookup with multiple criteria, proposed by Kris Jaganah:
=LET(
a,
FILTER(
HSTACK(
Time,
Purity),
Milk=K4),
XLOOKUP(
K5,
TAKE(
a,
,
1),
XLOOKUP(
K6,
Temp,
DROP(
a,
,
1),
,
-1),
,
-1))Excel solution 2 for Two-Way approximate Lookup with multiple criteria, proposed by Julian Poeltl:
=INDEX(
Purity,
XMATCH(
K4&K5,
Milk&Time,
-1),
XMATCH(
K6,
Temp,
-1))
I'm not so sure about the approximation - the nearest value would be more accurate than the same or smaller value.
But with the match mode nearest value my result would be 0.768197932482401 - so insted of 150s and 140.6°C 200s and 150.7°C (both for premium milk)Excel solution 3 for Two-Way approximate Lookup with multiple criteria, proposed by Milan Shrimali:
=INDEX(
D5:H15,
XMATCH(
K5&K6,
B5:B15&C5:C15,
-1),
XMATCH(
K7,
D4:H4,
-1))