2 different table correlation of 3 columns

[snifer]

I need to find out how to find the right result for the two column exchange rate and avg.rate based on the match of column "month" "year" and "currency" 

Economic						change in DKK
amount	currency	month	year			currency	month	year	exchange rate	avg. rate
10	EUR	1	2018			EUR	1	2018	1.5	1.3
20	CZK	2	2018			CZK	2	2018	7	7.5
result
amount	currency	month	year	exchange rate	avg. rate
10	EUR	1	2018	1.5	1.3
20	CZK	2	2018	7	7.5

[v-piga-msft]

Hi @snifer,

By my tests, the solution of AlB and Williamspsouza should be helpful.

You could download my attachment which is my test pbix. You could see the detail steps under APPLIED steps in Query Editor about Merge query.

In addition, you could create the calculated column with LOOKUPVALUE fucntion like below.

NewColumnExRate = 
LOOKUPVALUE (
    'change in DKK'[exchange rate],
   'change in DKK'[currency], 'Economic'[currency],
    'change in DKK'[month ], 'Economic'[month],
    'change in DKK'[year], 'Economic'[year]
)

NewColumnAveRate = 
LOOKUPVALUE (
    'change in DKK'[avg. rate],
   'change in DKK'[currency], 'Economic'[currency],
    'change in DKK'[month ], 'Economic'[month],
    'change in DKK'[year], 'Economic'[year]
)

In addition, based on the syntax of LOOKUPVALUE, it allows more than one search_columnName and search_value.

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)

Here is the output for two solutions.

Best Regards,

Cherry

[snifer]

change in DKK
currency	month	year	exchange rate	avg. rate	source
EUR	1	2018	1.5	1.3	actual
CZK	2	2018	7	7.5	actual
EUR	1	2018	2	1	budget
CZK	2	2018	7.1	7.6	budget

the problem was I didn't notice there was an extra column specifying the source of the exchange rate ( there are double result based on different source)

2 solutions:

or I split the table into 2 different tables based on source column

or better can I modify the code

NewColumnExRate = 
LOOKUPVALUE (
    'change in DKK'[exchange rate],
   'change in DKK'[currency], 'Economic'[currency],
    'change in DKK'[month ], 'Economic'[month],
    'change in DKK'[year], 'Economic'[year]
)

adding the possibility to take just one parameter of source like "actual"I

what I should use early or filter?

[v-piga-msft]

Hi @snifer,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[AlB · Accepted solution]

@snifer

NewColumnExRate = 
LOOKUPVALUE (
    'change in DKK'[exchange rate],
   'change in DKK'[currency], 'Economic'[currency],
    'change in DKK'[month], 'Economic'[month],
    'change in DKK'[year], 'Economic'[year],
    'change in DKK'[source], "actual"
)

[AlB]

@snifer

I'm not sure what you did. I just ran a quick test and it does work as expected.

[Anonymous]

Hi @snifer,

Have you tried the old school method; Create a calculated column concatenating currency, month and year and linking the tables by this created calculated column?

Best,

David

[Anonymous]

If both tables are connected and there is 1 to 1, you can use two calculated columns: 1. Related(avg.rate) 2. Related(exchange rate)

[snifer]

the table can be connected, so only option look to be lookup with 3 condition, but i can't make it work

[Anonymous]

You can use "Merge Query as new" and add two columns... let Source = Table.NestedJoin(Economic,{"currency"},#"change in DKK",{"currency"},"change in DKK",JoinKind.LeftOuter), #"Expanded change in DKK" = Table.ExpandTableColumn(Source, "change in DKK", {"exchange rate", "avg. rate"}, {"change in DKK.exchange rate", "change in DKK.avg. rate"}) in #"Expanded change in DKK"

[snifer]

merging a query you can do based on 1 column not in 3

[Anonymous]

You can base on 3... let Source = Table.NestedJoin(Economic,{"currency", "month", "year"},#"change in DKK",{"currency", "month ", "year"},"change in DKK",JoinKind.LeftOuter), #"Expanded change in DKK" = Table.ExpandTableColumn(Source, "change in DKK", {"exchange rate", "avg. rate"}, {"change in DKK.exchange rate", "change in DKK.avg. rate"}) in #"Expanded change in DKK"

[AlB]

what is the exact problem with LOOKUPVALUE()??

[AlB]

@snifer

Column for exchange rate in Table1 (the one you show under 'economic'). I haven't tested but it should work

NewColumnExRate =
LOOKUPVALUE (
    Table2[exchange rate],
    Table2[Currency], Table1[Currency],
    Table2[Month], Table1[Month],
    Table2[Year], Table1[Year]
)

[snifer]

based on you formula i create the one

NewColumnAVG-rate = LOOKUPVALUE ( ExchangeRates[DefaultAvgRate], ExchangeRates[CurrencyCode], ConsolidatedAccounts[CurrencyCode], ExchangeRates[Month], ConsolidatedAccounts[Month], ExchangeRates[Year], ConsolidatedAccounts[Year] )

but return an empty column

[snifer]

It accept just fir column then it doesn't take in consideration other 2,

can someone code this with lookup and all 3 columns?

[AlB]

@snifer

Ok, can you just show what you tried with LOOKUPVALUE that does not work and the error message you get?

[snifer]

no error, just not working get wrong value,,

can you try yourself lookup in the example and if work posting to me

[Anonymous]

Are you sure? I have tried here and it did work. This formula consider all 3 columns.. it's a merge....

[snifer]

im not familiar with merge by formula usually i use the interface, how i can merge with the formula?

[Anonymous]

Edit Queries (in the query editor) Select the table Economic then Merge Query as New. In the Merge window then select "Economic" and all 3 columns then select the second table "change in DKK" and all 3 columns also, then click "ok"... the new table contains everything you need.