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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
lgo
Frequent Visitor

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

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! 🙂

 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @lgo, power query version:

 

Result

dufoq3_0-1711391689205.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

Hi @lgo, power query version:

 

Result

dufoq3_0-1711391689205.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

lgo
Frequent Visitor

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.

 

 

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Greg_Deckler
Super User
Super User

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors