Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 Zhi
If 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 |
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 Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.