How do I return the value in a column that closest matches another column in Power Query

Question

I am trying to calculate the closest value to the Expected column based on 3 other columns (Week 1, Week 2, Week 3) of data in Power Query

Client	Expected	Week 1	Week 2	Week 3	Closest
Client 1	153	153	148	148	?
Client 2	3479	3464	3263	3310	?
Client 3	2951	2965	3211	3220	?

Normally, I use this Index/Match formula in a table:
=INDEX(Data[@[Week 1]:[Week 3]],MATCH(MIN(ABS(Data[@[Week 1]:[Week 3]-[@Expected])),ABS(ABS(Data[@[Week 1]:[Week 3]-[@Expected]),0))

I am trying to find the equivalent formula for Power Query and googling has left me empty and frustrated.

I have tried calculating a column
=Number.Abs(List.Min({[Week 1],[Week 2],[Week 3]})-[Expected])

to get the smallest difference but I am not sure how to take the next step and select the value in the column that has the smallest difference.

Answer 1

You need to calculate the absolute differences for Week 1 Week 2 and Week 3 compared to Expected and extract the smallest value from the DifferenceList.

Then you find the week value that matches the MinimumDifference and you can remove the DifferenceList and MinimumDifference columns.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUTBU0lEyNDVGkCYWUDJWB67ICChkbGJuCabMTECUkRlItbGxoQGyQpCYkaWpIZgyMwUrNDQEU0ZAhbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Expected = _t, #"Week 1" = _t, #"Week 2" = _t, #"Week 3" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Expected", Int64.Type}, {"Week 1", Int64.Type}, {"Week 2", Int64.Type}, {"Week 3", Int64.Type}}),

    #"Add Difference List" = Table.AddColumn(#"Type modifié", "DifferenceList", each {Number.Abs([#"Week 1"] - [Expected]), Number.Abs([#"Week 2"] - [Expected]), Number.Abs([#"Week 3"] - [Expected])}),

    #"Add Minimum Difference" = Table.AddColumn(#"Add Difference List", "MinimumDifference", each List.Min([DifferenceList])),

    #"Add Closest Value" = Table.AddColumn(#"Add Minimum Difference", "Closest", each List.First(List.Select({[#"Week 1"], [#"Week 2"], [#"Week 3"]}, (x) => Number.Abs(x - [Expected]) = [MinimumDifference]))),

    #"Removed Extra Columns" = Table.RemoveColumns(#"Add Closest Value",{"DifferenceList", "MinimumDifference"})
in
    #"Removed Extra Columns"