How to do vlookup with many conditions depending on dates, and more columns in power query

[lgo]

Hello!

I currently have two tables that give me two different type of informations. Table1 shows me the trucks that have entered France with "plate", "entry date" and "out date" included.

Truck Plate      Entry Date      Out Date

1234           21/03/2024   25/03/2024

2548           21/03/2024   25/03/2024

3658           21/03/2024   25/03/2024

1234           19/03/2023   20/03/2023

2548           17/03/2023   28/03/2023

3658           10/03/2022   11/03/2022

And Table2 provides information about full trip performed by the truck, including what the truck was transporting.

Truck Plate  Trip Start Date  Trip Finish Date  Material Transported  

1234              19/03/2024    30/03/2024        Wood

2548              19/03/2024    30/03/2024        Stones

3658              19/03/2024    30/03/2024        Steel   

1234              15/03/2023    25/03/2023        Cement

2548              12/03/2023    30/03/2023        Wood

3658              5/03/2022      17/03/2022        Stones

What I would need to know is what is the material that was transported when it entered in France (table above), but with the "material" from the table below. Something like:

If (Table1[Truck Plate] = Table2 [Truck Plate]) and (Table1[Entry Date] is >= Table2[Trip Start Date] and Table1[Entry Date] is <= Table2[Trip Finish Date]), then give me Table2[Material] else null

Then I would get something like that:

Truck Plate      Entry Date      Out Date  Material

1234           21/03/2024   25/03/2024    Wood

2548           21/03/2024   25/03/2024    Stones

3658           21/03/2024   25/03/2024    Steel

1234           19/03/2023   20/03/2023    Cement

2548           17/03/2023   28/03/2023    Wood

3658           10/03/2022   11/03/2022    Stones

Something like that. I know how to do it in excel, but so far i have been successfully doing merges in the data with power query, but not sure that in this case this would work... could somebody give me a hand? thanks a lot in advance and thanks! 🙂

[dufoq3 · Accepted solution]

Hi @lgo, power query version:

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTIy1Dcw1jcyMAJzTOGcWJ1oINfEgoASYzNTQkqgFhlaQkWNQUoM4BwkiwzNkZVYoCiBWmQI02gE4hjCObGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Truck Plate" = _t, #"Entry Date" = _t, #"Out Date" = _t]),
    ChangedTypeTable1 = Table.TransformColumnTypes(Table1,{{"Entry Date", type date}, {"Out Date", type date}}, "sk-SK"),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTK01Dcw1jcyMAJxjA2QOOH5+SlKsTrRSkamJhZ4VQaX5OelFoPVGpuZElKbmpoDVgpzgClU1hjIMULmOKfmpuaVoDjBCEkabqwxsmOhDoCZYwTSZY7Egbk1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Truck Plate" = _t, #"Trip Start Date" = _t, #"Trip Finish Date" = _t, #"Material Transported" = _t]),
    ChangedTypeTable2 = Table.TransformColumnTypes(Table2,{{"Trip Start Date", type date}, {"Trip Finish Date", type date}}, "sk-SK"),
    MergedQueries = Table.NestedJoin(ChangedTypeTable1, {"Truck Plate"}, ChangedTypeTable2, {"Truck Plate"}, "Table2", JoinKind.LeftOuter),
    Ad_Material = Table.AddColumn(MergedQueries, "Material", each Table.SelectRows([Table2], (x)=> x[Trip Start Date] <= [Entry Date] and x[Trip Finish Date] >= [Out Date])[Material Transported]{0}?, type text),
    RemovedColumns = Table.RemoveColumns(Ad_Material,{"Table2"})
in
    RemovedColumns

[lgo]

Thanks a lot to both for the quick response. I am currently trying to go with @dufoq3 answer, cause I am more familiar with it. However I get lost in a point. My tables are separated in two different queries... can I add a merge query in one of both queries? (i mean in power query for excel?).

Cause not sure I know how to implement that, once i have the data in two different queries. So not sure how can i call table2 in table 1 editor version.

Thanks a lot agian,

Laura.

[dufoq3]

In power query you can merge any queries you want 😉 (and yes, it is possible also in Power Query for Excel).

In my query: Replace whole code for Table1 with your table reference. Do the same for Table2. If you don't know what I mean. Check Note below, just imagine that Source as Table1 and Table2.

[Greg_Deckler]

@lgo Well, the DAX for this is pretty simple, I would think that the Power Query would be similar but with a merge based on truck plate (all from left, all matching from right). Then a column expansion, a calculated column to determine if it is in the right range of dates (if then else) and then a filter on that selection column, remove the calculated column.

Column = 
  VAR __Truck = [Truck Plate]
  VAR __Entry = [Entry Date]
  VAR __Out = [Out Date]
  VAR __Result = MAXX( FILTER( 'Table2', [Truck Plate] = __Truck && [Trip Start Date] < [__Entry] && [Trip Finish Date] > __Out), [Material Transported] )
RETURN
  __Result