Retrieve a value of a column from another table

[ovetteabejuela]

Hi All,

Thanks to this community I've learned how to implement this using crossjoin, however what if I need to retrieve the value to a column of an existing table(Transaction Table)  and not from a new table generated by a Join(crossjoin)

Also, I think it's NOT possible to create a relationship between Transaction Table and Rates Table

For example on Transaction Table I want to retrieve the applicable rate for 12/1/2016 which should be 5.5.

Transaction Date	Historical Rate(s)		<<< Transaction Table | Rates Table >>>		Effective Start Date	Effective End Date	Rate
12/1/2016			<<< Transaction Table | Rates Table >>>		12/1/2016	12/4/2016	5.5
12/2/2016			<<< Transaction Table | Rates Table >>>		12/5/2016	12/9/2016	7.8
12/3/2016			<<< Transaction Table | Rates Table >>>		12/10/2016	12/14/2016	6.7
12/4/2016			<<< Transaction Table | Rates Table >>>
12/5/2016			<<< Transaction Table | Rates Table >>>
12/6/2016			<<< Transaction Table | Rates Table >>>
12/7/2016			<<< Transaction Table | Rates Table >>>
12/8/2016			<<< Transaction Table | Rates Table >>>
12/9/2016			<<< Transaction Table | Rates Table >>>
12/10/2016			<<< Transaction Table | Rates Table >>>
12/11/2016			<<< Transaction Table | Rates Table >>>
12/12/2016			<<< Transaction Table | Rates Table >>>
12/13/2016			<<< Transaction Table | Rates Table >>>
12/14/2016			<<< Transaction Table | Rates Table >>>

[androo235]

Thanks and the animations are useful, except, and this may be nothing to do with you, once they are running they endlessly repeat and there appears to be no way to pause them or know if you are looking at the begining middle or end of the animation.

[Sean · Accepted solution]

@ovetteabejuela

First perform these steps in the Query Editor

1) Select the 2 date columns and change data type to whole number

2) add a custom column as in the attached image ={ .. }

3) expand to new rows and rename

4) convert back to dates

5) close and apply

then create this COLUMN in your Transactions table (no relationship needed)

Historical Rate COLUMN =
LOOKUPVALUE (
    'Rates Table'[Rate],
    'Rates Table'[Date], 'Transactions Table'[Transaction Date]
)

and here's the result

Hope this helps!

[SunnyRKFOODLAND]

is there any way to do this power query ?

[Anonymous]

Hi,

I tested with this condition as "lookup". It works. And now, I would like to test with the "IF" instead of "LOOKUP" in power BI Query and no Desktop, because, I would like to test with the 2 values ​​"min" and "max".

For instance : 

Table A                             Table B

Column Age                    Code1    Min     Max    Desc

0                                        Age       0          2       between 0 and 2 years old

2                                        Age       3        10       between 3 and 10 years old.       

5

Table A.ColumnA  > TableB. Column "Min" and Table A.ColumnA  > TableB. Column "Max" with table.Code1 ="Age"

value of return  : Desc 

How to do it ?

Thank you in advance for your answer

[Phil_Seamark]

That is pretty cool

[ovetteabejuela]

Hahaha, I learned something new again...

Thank you @Sean, another clever way to solve my problem - this has been troubling me for almost a day now and I think what you've showed is the solution!