Forum Discussion
Power Query lookup against a filtered table
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.
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 Zhi
If 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 |
Are you sure that the second example is setup correctly? I don't see the pattern.
Hi,
I had a look at the data and they do seem to reflect the expected return.
At a high level, Table A represents a recurring schedule work whilst Table B represents unscheduled reactive work hence may or may not exists.
What i'm trying to derieve is for each record in Table A, return the last time the object had a unscheduled work completed BUT the return record must be prior to the date in the Table A record.
i.e. for the 3 Table A record
Record 1 with date 01/01/2019 would return 05/01/2000 from Table B as that is the largest date that is prior to 01/01/2019
Record 2 with date 01/05/2019 would return 30/04/2019 from Table B as that is the largest date that is prior to 01/05/2019
Record 3 with date 01/10/2019 would return 30/04/2019 from Table B as that is the largest date that is prior to 01/10/2019
Hi S_loke,
Did this help you solve your issue? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.