Forum Discussion

S_loke's avatar
S_loke
Advocate I
6 years ago
Solved

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 str...
Need Help
  • dax's avatar
    dax
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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

S_loke's avatar
S_loke
Advocate I
6 years ago

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

 

 

 

  • dax's avatar
    dax
    Community Support
    6 years ago

    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.