Forum Discussion
DAX > M Query - LOOKUPVALUE
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 |
13 Replies
- lbendlin
Super User
use Table.AddColumn() with a custom function as column generator. Inside the function you can apply the lookup to your reference table.
- amitchandak
Anonymous , refer if this can help
https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/
- 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
v-juanli-msftCommunity Support
Hi Anonymous
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.- v-juanli-msft
Hi Anonymous
(x) => Table.SelectRows(#"FV-ToDt",
each [Employee ID] = x[[Employee ID]
and [To Date]=x[From Date]
)[[ should be [.
Best Regards
Maggie
