Forum Discussion
Power Query lookup against a filtered table
Hi,
Currently i have the requirement to build out a table which requires a conditional lookup to return a max value BUT only if the value fits a specific condition.
2 tables with the same structure, common object used in both tables being M:M. for each record in table A, i'm wanting to return the maximum transaction date from table B where it is not after the transaction date in table A
Table A
| Obj | Transaction Date |
| A | 01/01/2019 |
| A | 01/02/2019 |
| A | 01/03/2019 |
| A | 01/04/2019 |
| A | 01/05/2019 |
| A | 01/06/2019 |
| A | 01/07/2019 |
| A | 01/08/2019 |
| A | 01/09/2019 |
| A | 01/10/2019 |
Table B
| Obj | Transaction Date |
| A | 01/01/2000 |
| A | 01/04/2019 |
| A | 01/10/2019 |
Expected results from lookup
| Obj | Transaction Date | Lookup |
| A | 01/01/2019 | 01/01/2000 |
| A | 01/02/2019 | 01/01/2000 |
| A | 01/03/2019 | 01/01/2000 |
| A | 01/04/2019 | 01/04/2019 |
| A | 01/05/2019 | 01/04/2019 |
| A | 01/06/2019 | 01/04/2019 |
| A | 01/07/2019 | 01/04/2019 |
| A | 01/08/2019 | 01/04/2019 |
| A | 01/09/2019 | 01/04/2019 |
| A | 01/10/2019 | 01/10/2019 |
Currently i can solve this in DAX using the codes below but am unable to find a viable solution using M.
Lookup Max Date =
VAR Data_record = 'Data records'[Object]
VAR Data_date = 'Data records'[Object Date]
RETURN
CALCULATE (
MAX ( 'Lookup Table'[Lookup Date] ),
'Lookup Table'[Lookup Object] = Data_record,
'Lookup Table'[Lookup Date] <= Data_date
)
I tried to replicate this in M through writing a function but performance meant this was not viable (probably doing it wrong as well)
= (colEquipment as text, colFilteredDate as date, lookupcolEquipment as text, lookupcolFilteredDate as date, lookupTable as table) =>
let
Lookup = Table.Max(Table.SelectRows(lookupTable,each lookupcolEquipment = colEquipment and lookupcolFilteredDate <= colFilteredDate), "Basic Finish Date")
in
Lookup
Is there any suggestion on M codes or a different way to tackle this problem?
Shaun
Hi S_loke
You could try to refer to below M code(in addition, I think your date in Table 2019/1/5 is 2019-May-1 instead of 2019-jan-5)
let Source = Table.NestedJoin(TableA, {"Obj"}, TableB, {"Obj"}, "TableB", JoinKind.FullOuter), #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Transaction Date"}, {"TableB.Transaction Date"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded TableB", "Custom", each if [Transaction Date] >= [TableB.Transaction Date] then [TableB.Transaction Date] else null), #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Obj", "Transaction Date"}, {{"max", each List.Max([Custom]), type date}}) in #"Grouped Rows"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi S_loke,
You could try below M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwNDS6VYHYSQEaaQMaaQCaaQKaaQGaaQOaaQBaaQJYaQoQFUKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Obj = _t, #"Transaction Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Obj", type text}, {"Transaction Date", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Transaction Date"}, TableB, {"Transaction Date"}, "TableB", JoinKind.FullOuter), #"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"Transaction Date"}, {"TableB.Transaction Date"}), #"Sorted Rows" = Table.Sort(#"Expanded TableB",{{"Transaction Date", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"TableB.Transaction Date"}), #"Removed Top Rows" = Table.Skip(#"Filled Down",1) in #"Removed Top Rows"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for that - interesting approach but whlist it worked for that specific example, that is only due to the simplicity of the data.
If I add additional records with dates that reflect the actual problem more accurately, your prosposed solution is no longer viable.
Table A
Obj Transaction Date A 01/01/2019 A 01/02/2019 A 01/03/2019 A 01/04/2019 A 01/05/2019 A 01/06/2019 A 01/07/2019 A 01/08/2019 A 01/09/2019 A 01/10/2019 B 01/01/2019 B 01/02/2019 B 01/03/2019 B 01/04/2019 B 01/05/2019 B 01/06/2019 B 01/07/2019 B 01/08/2019 B 01/09/2019 B 01/10/2019 C 01/01/2019 C 01/02/2019 C 01/03/2019 C 01/04/2019 C 01/05/2019 C 01/06/2019 C 01/07/2019 C 01/08/2019 C 01/09/2019 C 01/10/2019 Table B
Obj Transaction Date A 05/01/2000 A 30/04/2019 A 05/10/2019 B 20/01/2000 B 10/10/2019 C 15/08/2000 C 02/02/2019 Expected result
Obj Transaction Date Lookup A 01/01/2019 05/01/2000 A 01/02/2019 05/01/2000 A 01/03/2019 05/01/2000 A 01/04/2019 05/01/2000 A 01/05/2019 30/04/2019 A 01/06/2019 30/04/2019 A 01/07/2019 30/04/2019 A 01/08/2019 30/04/2019 A 01/09/2019 30/04/2019 A 01/10/2019 30/04/2019 B 01/01/2019 20/01/2000 B 01/02/2019 20/01/2000 B 01/03/2019 20/01/2000 B 01/04/2019 20/01/2000 B 01/05/2019 20/01/2000 B 01/06/2019 20/01/2000 B 01/07/2019 20/01/2000 B 01/08/2019 20/01/2000 B 01/09/2019 20/01/2000 B 01/10/2019 20/01/2000 C 01/01/2019 15/08/2000 C 01/02/2019 15/08/2000 C 01/03/2019 02/02/2019 C 01/04/2019 02/02/2019 C 01/05/2019 02/02/2019 C 01/06/2019 02/02/2019 C 01/07/2019 02/02/2019 C 01/08/2019 02/02/2019 C 01/09/2019 02/02/2019 C 01/10/2019 02/02/2019
