Search code examples
powerbidaxpowerquerypowerbi-desktop

Custom Column in Power Query Editor - Look up value in another table based on multiple criteria

So first of all I am new to Power Query Editor or M as it seems to be know as.

I have two tables, as below...

Job Table

  • CreateDate
  • PlacementEndDate
  • ComputedEndDate (achieved in Power BI as a custom column, basically if placementenddate is null it defaults to createdate)

and

PlacementWeeks

  • WeekStart (always a sunday)
  • WeekEnd (always a saturday)

I am trying to add a custom column in Power Query editor to the job table which is as below...

let mydate=[ComputedEndDate] in 
Table.Max(
Table.SelectRows(
#"PlacementWeeks", each [WeekStart] >= mydate and [WeekEnd] <= mydate),
"WeekEnd")

[WeekEnd]

The column just returns error, for context as 'ComputedEndDate' falls back to createdate if placementenddate is null then I am trying to make sure that I can always find the closest Saturday to the ComputedEndDate column from the placementweeks table and returning this.

The placements weeks table is a big list of sundays and saturdays going on well into the future.

My question is what am I doing wrong in the M query firstly, secondly I am wondering if this is actually the best place to do this, should I be doing this as a measure or column outside of the data modelling and use DAX?

Any help would be most appreciated.

See below for image of error enter image description here

Update

Thanks to a combination of horseyride,spinfuzer and me correcting my logic which was just plain wrong,see below for final working comlumn...

enter image description here

Solution

  • Try

    List.Max(
Table.SelectRows(
#"PlacementWeeks", (x) => x[WeekStart] >= [ComputedEndDate] and x[WeekEnd] <= [ComputedEndDate])
[WeekEnd]
)

    This does feel like it would be slow if you had a large data set. The x prefix refers to the columns in your PlacementWeeks table and no prefix (or _ prefix) refers to the columns in your Job Table.