cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
S_loke Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: 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 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
Community Support Team
Community Support Team

Re: Power Query lookup against a filtered table

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.

S_loke Regular Visitor
Regular Visitor

Re: Power Query lookup against a filtered table

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

 

mcybulski Established Member
Established Member

Re: Power Query lookup against a filtered table

Are you sure that the second example is setup correctly? I don't see the pattern.

S_loke Regular Visitor
Regular Visitor

Re: Power Query lookup against a filtered table

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

 

 

 

Community Support Team
Community Support Team

Re: 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 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

Community Support Team
Community Support Team

Re: Power Query lookup against a filtered table

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors