Power Query conditional join-CodePudding

Good morning gentlemen, I hope you will help me to solve this issue. I am trying to merge two tables in PowerQuery according to the following logic:

This is the first Table

and this is my second

I have merged the two as follows through a full outer join using the ID column

What I am trying to achieve is this output

So what I was trying o achieve is: How can I tell Power Query to give me the [day range] column (from the second table) for each record if [Departure time] is between [from] and [to]?

I am new to PowerQuery, so please try to explain your solution like if I am a kid from the elementary school.

Many thanks to all the people finding the time to solve this problem.

CodePudding user response：

In the second table, in powerquery, add column, custom column with formula

= {  [#"from (mins)"]  ..  [#"to (mins)"]  }

use double arrows atop the new column to Expand to new rows

it will look like

File .. close and load to .. only create connection

Now go to first table.

Do a merge. Merge on ID column and on the new Custom column to the Deparure time column with left outer join

Expand the result to pull in the day range using the arrows atop the new column

full sample code for table 1

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source,{"ID", "Deparure time (in mins)"},Table2,{"ID", "Custom"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"day Range"}, {"day Range"})
in  #"Expanded Table2"