How to joint two tables based on values

[SylwiaK]

Hello,

could you help me with the following problem?

I have two independent tables A and B. In A data is stored with start, end and type of activity. In B data is stored with beginning of some measurement. I want to add a column to B so that it shows the type of activity. The type shall be taken from A based on the beginning, if begininning is between start and end. How to write such query?

Tried: 

= Table.AddColumn(TableB, "Activity Type", each List.First(List.Select(TableA[Type], [Beginning] >= TableA[Start] and [Beginning] <= TableA[End])), type text)

but it cannot be executed due to cyclic references.

I do not want buffers as I need to sync data often.

Best 

Sylwia

[amitchandak]

@SylwiaK , refer if this can help

Power Query Across Table calculated column: https://youtu.be/InjrmY-LJdA

[SylwiaK]

@amitchandak I tried one more time following your tutorial and it almost works 😉

The problem is that the values of columns are not compared, i.e.:

= Table.AddColumn(#"Changed Type", "Custom", each let
col = [Beginning],
_table = Table.SelectRows(glds_export, each [END] <= col),
count = Table.RowCount(_table)
in
count)

results in all rows selected...

Three columns are of the type Date/Time.

Would you have any idea how to cope with that?

[SylwiaK · Accepted solution]

Managed to figure out how to solve the problem - used the solution by @amitchandak but had to change type of data to numeric and then it compared the values correctly 

[SylwiaK]

Hi @amitchandak,

thanks for the response, unfortunately it has the same result - cyclic references error