Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
S_loke
Advocate I
Advocate I

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

ObjTransaction Date
A01/01/2019
A01/02/2019
A01/03/2019
A01/04/2019
A01/05/2019
A01/06/2019
A01/07/2019
A01/08/2019
A01/09/2019
A01/10/2019

 

Table B

ObjTransaction Date
A01/01/2000
A01/04/2019
A01/10/2019

 

Expected results from lookup

ObjTransaction DateLookup
A01/01/201901/01/2000
A01/02/201901/01/2000
A01/03/201901/01/2000
A01/04/201901/04/2019
A01/05/201901/04/2019
A01/06/201901/04/2019
A01/07/201901/04/2019
A01/08/201901/04/2019
A01/09/201901/04/2019
A01/10/201901/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

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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.

View solution in original post

6 REPLIES 6
dax
Community Support
Community Support

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"

400.PNG

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

ObjTransaction Date
A01/01/2019
A01/02/2019
A01/03/2019
A01/04/2019
A01/05/2019
A01/06/2019
A01/07/2019
A01/08/2019
A01/09/2019
A01/10/2019
B01/01/2019
B01/02/2019
B01/03/2019
B01/04/2019
B01/05/2019
B01/06/2019
B01/07/2019
B01/08/2019
B01/09/2019
B01/10/2019
C01/01/2019
C01/02/2019
C01/03/2019
C01/04/2019
C01/05/2019
C01/06/2019
C01/07/2019
C01/08/2019
C01/09/2019
C01/10/2019

 

Table B

ObjTransaction Date
A05/01/2000
A30/04/2019
A05/10/2019
B20/01/2000
B10/10/2019
C15/08/2000
C02/02/2019

 

Expected result

ObjTransaction DateLookup
A01/01/201905/01/2000
A01/02/201905/01/2000
A01/03/201905/01/2000
A01/04/201905/01/2000
A01/05/201930/04/2019
A01/06/201930/04/2019
A01/07/201930/04/2019
A01/08/201930/04/2019
A01/09/201930/04/2019
A01/10/201930/04/2019
B01/01/201920/01/2000
B01/02/201920/01/2000
B01/03/201920/01/2000
B01/04/201920/01/2000
B01/05/201920/01/2000
B01/06/201920/01/2000
B01/07/201920/01/2000
B01/08/201920/01/2000
B01/09/201920/01/2000
B01/10/201920/01/2000
C01/01/201915/08/2000
C01/02/201915/08/2000
C01/03/201902/02/2019
C01/04/201902/02/2019
C01/05/201902/02/2019
C01/06/201902/02/2019
C01/07/201902/02/2019
C01/08/201902/02/2019
C01/09/201902/02/2019
C01/10/201902/02/2019

 

dax
Community Support
Community Support

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.

Anonymous
Not applicable

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 

Capture.JPG

 

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

 

 

 

dax
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors