DAX > M Query - LOOKUPVALUE

[jasontran]

Any tips on how to convert this DAX over to M?

The below displays as expected but I need the transformation to occur in Power Query to conduct subsequent steps on the existing [From Date] and generated [New To Date]

New To Date = SWITCH( TRUE(),
Recall[Type]="Recall" && Recall[Flag]<>BLANK(),
LOOKUPVALUE(Original[To Date],
Original[Letter Date],Recall[Original Date],
Original[ID],Recall[ID],
BLANK()
),
BLANK()

)

Sample data as follows

[Original]

ID	Type	Letter Date	Original Date	From Date	To Date	Flag
A	Order	29-Apr-20	17-Apr-20	13-Apr-20	31-May-20	Yes
A	Recall	28-May	29-Apr-20	25-May-20		Yes
B	Order	11-May-20		26-May-20	23-Jun-20	Yes
B	Recall	09-Jun-20	11-May-20	17-Jun-20		Yes

[Recall]

ID	Type	Letter Date	Original Date	From Date	To Date	Flag	New To Date
A	Recall	28-May-20	29-Apr-20	25-May-20		Yes	31-May-20
B	Recall	09-Jun-20	11-May-20	17-Jun-20		Yes	21-Jun-20

[v-juanli-msft]

Hi @jasontran 

I would value lbendlin's answer.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKTU7MyQEyjCx0fRMrdY0MQGxLXceCIijbFCEORJGpxUqxOtFKTsh6LXW9SvMgSgwNEcoNzRHiMK2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, #"Letter Date" = _t, #"Original Date" = _t, #"From Date" = _t, #"To Date" = _t, Flag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Letter Date", type date}, {"Original Date", type date}, {"From Date", type date}, {"To Date", type text}, {"Flag", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=>Table.SelectRows(Original,
            each [ID]=x[ID] and [Letter Date]=x[Original Date])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"To Date"}, {"Custom.To Date"})
in
    #"Expanded Custom"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jasontran]

I've tried to apply the logic via a Custom Column but am getting a "Token Comma expected" error.

(x) => Table.SelectRows(#"FV-ToDt",
each [Employee ID] = x[[Employee ID]
and [To Date]=x[From Date]
)

[v-juanli-msft]

Hi @jasontran 

(x) => Table.SelectRows(#"FV-ToDt",
each [Employee ID] = x[[Employee ID]
and [To Date]=x[From Date]
)

[[ should be [.

Best Regards

Maggie

[jasontran]

Still getting an error. This time it's highlighted the end close bracket of the formula

[v-juanli-msft]

Hi @jasontran 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @jasontran 

To help you slove it, please show the code below from your query.

******
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=>Table.SelectRows(Original,each [ID]=x[ID] and [Letter Date]=x[Original Date])),
*****

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

show the entire line of code

[amitchandak]

@jasontran , refer if this can help

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

[jasontran]

How would you nest an IF statement within this?

[lbendlin]

Nesting ifs in Power Query is very simplistic

if this then that else if this1 then that1 else if this2 then that2 else this3

[jasontran]

i meant in the context of utilizing a lookup. that doesnt seem possible with the need to create a function in a separate query for a lookup

[lbendlin]

yes, you can use that inside the function without issues.

Here's an example

#"Added Custom" = Table.AddColumn(Index, "Match", 
        (k) => Table.SelectRows(#"Assignments",
            each ([AID]="*" or k[AID]=[AID])
                and ([PSA]="*" or k[PSA]=[PSA])
                and ([SID]="*" or k[SID]=[SID])
                )
            ),

[lbendlin]

use Table.AddColumn() with a custom function as column generator. Inside the function you can apply the lookup to your reference table.