Inserting a values of one column from a table into another table

Question

I am looking for a ways to implement in power query or by using DAX

I do have two tables

Table_1 is as shown below:

Date          Simple    ActualValue    Result
15.07.2023      A         Null       Result from Table_2 considering date 
15.07.2023      B          20           20
15.07.2023      C          30           30
15.07.2023      D          40           40
15.07.2023      E          20           20
15.07.2023      F          12           12
15.07.2023      G         Null      Result from Table_2 considering date 
16.07.2023      A         Null      Result from Table_2 considering date
16.07.2023      B          35        35
16.07.2023      C          42        42
16.07.2023      D          53        55
16.07.2023      E          60        60
16.07.2023      F          17        17
16.07.2023      G         Null      Result from Table_2 considering date

Table_2:

Complex  Field_1  Operator_1   Field2  Operator_2    Field_3    Formula()
A        B     +          C     Null         Null        B+C
G       E      *          D          /       F          E*D/F

Formula column in Table_2 is a dynamically merged column from Table_2 and

I am looking for ways to dynamically calculate result for the Formulas of Table_2 by looking up values from Table_1 considering the Dates

Please do provide some suggestions.

Answer 1

You can use Expression.Evaluate for this issue.
Also in the code below, I did NOT include the Formula() column in my source data, but rather generated the formula in situ showing only the result.

Paste the code below into the Advanced Editor. Then, if necessary, change the Table Names in the two Source lines to reflect your actual Table names in your workbook.

let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    Table_1 = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Simple", type text}, {"ActualValue", type nullable number}}),
    Lookup = Record.FromList(
                Table_1[ActualValue],
                Table_1[Simple]),

//Table_2
    Source2 = Excel.CurrentWorkbook(){[Name="Table_2"]}[Content],
    Table_2 = Table.TransformColumnTypes(Source2, List.Transform(Table.ColumnNames(Source2), each {_, type nullable text})),

//Calculate Formula
    #"Add Calc Formula" = Table.AddColumn(Table_2, "Formula Result", (rw)=>
        let 
            r = Record.RemoveFields(rw,"Complex"),
            str = Text.Combine(List.RemoveMatchingItems(Record.FieldValues(r),{"Null",null},Comparer.OrdinalIgnoreCase)," "),
            formula = Expression.Evaluate(str,Lookup)
        in 
            formula, type number)

in
    #"Add Calc Formula"

Table_1

Table_2

Result

Edit to account for different dates for different values
and also results being added to Table_1

New Table_1

Revised M Code

let

//Table_2
    Source2 = Excel.CurrentWorkbook(){[Name="Table_2"]}[Content],
    Table_2 = Table.TransformColumnTypes(Source2, List.Transform(Table.ColumnNames(Source2), each {_, type nullable text})),

//Table_1
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    Table_1 = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Simple", type text}, {"ActualValue", type nullable number}}),
    #"Grouped Rows" = Table.Group(Table_1, {"Date"}, {
        {"Result From Table_2", (t)=>
            let
                Lookup = Record.FromList(t[ActualValue],t[Simple]),  
                rws = Table.SelectRows(Table_2, each List.ContainsAny(Table_2[Complex], t[Simple])),
            
            //Calculate Formula
                #"Add Calc Formula" = Table.AddColumn(t, "Result From Table_2", (rw)=>
                    if rw[ActualValue] = null then
                    let 

                    //select the correct row from Table_2
                        r = Table.RemoveColumns(Table.SelectRows(Table_2, each [Complex]=rw[Simple]),"Complex"){0},

                    //Create the formula
                        str = Text.Combine(List.RemoveMatchingItems(Record.FieldValues(r),{"Null",null},Comparer.OrdinalIgnoreCase)," "),
                    
                    //evaluate the formula
                        formulaResult = Expression.Evaluate(str,Lookup)
                        
                    in 
                        formulaResult
                    else rw[ActualValue])
            in 
                #"Add Calc Formula",
                type table[Date=date, Simple=text, ActualValue=nullable number, Result From Table_2=number]}
        }),
    #"Expanded Result From Table_2" = Table.ExpandTableColumn(#"Grouped Rows", "Result From Table_2", 
        {"Simple", "ActualValue", "Result From Table_2"})
in
    #"Expanded Result From Table_2"

Results

Answer 2

Import Table1 and Table2 as queries.

Then make a "FormulaContext" query that returns a record of each field value. For example...

let
  Source = Table1,
  AsRecord = Record.FromList(
    Source[ActualValue],
    Source[Simple]
  )
in
  AsRecord

If you're doing what I think you're doing, you'll find that your formula evaluation will have quirks and exceptions, so it's best to create a separate function query that takes a row from Table2 and returns the output of the formula. Let's call it "fValueFromFormula", starting with code of

(r as record) => Expression.Evaluate(
  r[#"Formula()”],
  FormulaContext
)

and then if any steps involved in evaluating your queries get more complicated, defining it as a standalone function will make your code easier to reason through.

For a great understanding of Expression.Evaluate I highly recommend Chris Webb's blog post at blog.crossjoin.co.uk

Now you've got what you need and it's set up so nicely that the last step is boring!

Table.AddColumn(
  Table2,
  "Formula Result",
  fValueFromFormula,
  type number
)

The above will evaluate your formula into new column "Formula Result"!

Also, it gets more interesting. If you add functions to FormulaContext then (and only then) those functions can be used within your formulas! So, if the second argument of the above call to Expression.Evaluate was [#"Number.Ln" = Number.Ln] & FormulaContext then your formula would have access to do natural logs, and if the second argument was #shared & FormulaContext then your formulas could use any and every function available to you within power query because #shared is a special variable in power-query that returns everything available in the current context. Within Expression.Evaluate, it's whatever is passed in as the second argument, but directly within your queries it's a record of every function and every query! It's awesome.

You can also look at Chris Webb's blog for great posts on error handling, so when there's an error in a formula, you can try to respond with a descriptive message to help understand what's wrong.