Filtering one table inside the scope of AddColumn

[Oleksandr]

Hi experts!
Can someone point me in the right direction: I have two tables: the first is the main one and the second is supplementary, which contains different coefficients applicable based on the value of the column from the main table. Then the coefficient serves as a multiplier to the product of two values from the main table.

Main table

Col1	Col2	Col3	Added Column
1	2	A	Col1*Col2*Lookup in Table 2 based on Col3
3	4	B	Col1*Col2*Lookup in Table 2 based on Col3

Reference Table

Value	Coefficient
A	1
B	0.5

So far I come up with the following solution, which does not work because the reference to the value inside the Main table does not work inside the scope of the Table.Filter of the reference table.

#"Added Custom" = Table.AddColumn(

        MainTable,

        "Added Column",

        each

            [Col1]*

            [Col2]*

            List.First(

                Table.Column(

                    Table.SelectRows(

                        ReferenceTable,

                        each [Value] = [Col3]                   
                      ),

                    "Coefficient"

                )

            )

    )
Any advice is greatly appreciated!
Best,
Sasha

[edhans · Accepted solution]

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.

[Oleksandr]

Thank you very much! Extencivity of your kind reply fills a lot of gaps in my basic understanding of M! 🙂

[edhans]

Excellent @Oleksandr - I had this exact same question 2 years ago when I was getting started. Glad you are enjoying M. It can be fun to use.

[AlB]

Hi @Oleksandr 

See the attached file for a possible solution

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Oleksandr]

Thank you so much for your help! However, I'm working with Power Query inside of Excel, have not requested a Power Bi desktop yet. So if you could share the code, probably I could figure out from it. Thaks again!