powerbidaxpowerquerycustom-function

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

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.

Solution

  • 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
    enter image description here

    Table_2
    enter image description here

    Result
    enter image description here

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

    New Table_1
    enter image description here

    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
    enter image description here