Filtering one table inside the scope of AddColumn

You have to assign the value of Col3 to a variable so the inner Table.SelectRows can access it Oleksandr . See this code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkcgds7PMdQCEkZaPvn52aUFCpl5CiGJSTmpCkYKSYnFqSkK+XkKQHljpVidaCVjoA4TIHYiTWcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, #"Added Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}}),
    AddFromRefTable = 
        Table.AddColumn(
            #"Changed Type",
            "New Calculation",
            each
                let
                    varCol3 = [Col3]
                in
                Table.SelectRows(
                    #"Reference Table",
                    each [Value] = varCol3
                )[Coefficient]{0} * [Col1] * [Col2]
        )
in
    AddFromRefTable

 

It returns this:

Note that this will not perform well over tens of thousands of records as it will have to do tens of thousands of Table.SelectRows, but for smaller data sets it will work fine. This is what it does:

                Table.SelectRows(
                    #"Reference Table",
                    each [Value] = varCol3
                )[Coefficient]{0} * [Col1] * [Col2]

 

1. Returns a table where [Value] from the Reference table matches varCol3 which was previously assigned the value from [Col3] of the current table.
2. The [Coefficient] after the close paren of Table.SelectRows returns the Coefficient column as a list.
3. The {0} returns the first item in that list as a scalar value
4. That is then multiplied by Col1 and Col2 of the current table.

 

 

For large sets, just merge the Reference table into the main table, expand the Coefficient column, then do your math and remove unnecessary columns. That will do millions of records just fine.

Basic code for that just in case you've not done a Merge before. Assumes your 2nd table is called "Reference Table"

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkcgds7PMdQCEkZaPvn52aUFCpl5CiGJSTmpCkYKSYnFqSkK+XkKQHljpVidaCVjoA4TIHYiTWcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, #"Added Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Col3"}, #"Reference Table", {"Value"}, "Reference Table", JoinKind.LeftOuter),
    #"Expanded Reference Table" = Table.ExpandTableColumn(#"Merged Queries", "Reference Table", {"Coefficient"}, {"Coefficient"})
in
    #"Expanded Reference Table"

 

 

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.