Calculating a Column based on multiple criteria

Hi mdykes ,

I have reproduced some data sample according to the screenshot you provided and tranformed the table.

Here is the whole step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwVNJRMjI31TMzAjNM9MzM0RmmpkqxOiC1RiSoNSZBrQkJak1JUGtGnNpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Engine Power(kW)" = _t, Tile1 = _t, Tile2 = _t, Tile3 = _t, Tile4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Engine Power(kW)", Int64.Type}, {"Tile1", type number}, {"Tile2", type number}, {"Tile3", type number}, {"Tile4", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Engine Power(kW)"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Tile Type"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Tile","",Replacer.ReplaceText,{"Tile Type"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Engine Power(kW)", Int64.Type}, {"Tile Type", Int64.Type}, {"Value", type number}})
in
    #"Changed Type1"

Then you could use LOOKUPVALUE() function to create a column to find the matched value like this:

Emission rate =
LOOKUPVALUE ( 'Table'[Value], [Engine Power(kW)], [Eng], [Tile Type], [Tile] )

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.