Forum Discussion
How to joint two tables based on values
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
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
4 Replies
SylwiaK , refer if this can help
Power Query Across Table calculated column: https://youtu.be/InjrmY-LJdA
Hi amitchandak,
thanks for the response, unfortunately it has the same result - cyclic references error
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?
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
