M Code - two criteria for look up

Power Query
1

Dear DNA Team
Exchange rate.xlsx (19.9 KB)

There are two tables; T1=exchange rates, T2 = sales. I do not wish to merge them

I am trying to return the FX number base on two criteria (payment date and currency)

Currently I wrote the m code for one criteria:
if [Tran currency] = “eur” then 1 else Exchange{[Date=[Payment Date]]}

When I change the m code to:

if [Tran currency] = “eur” then 1 else Exchange{[Date=[Payment Date]]}{[Currency=[Tran currency]]}

I get an error:
We cannot convert a value of type Record to type List.

Could you please help?

Thank you,

Mateusz

2

Hi @Matty,

You were so close. :clap: :clap: :clap:
Give this a go instead.

Exchange{[Date=[Payment Date], Currency=[Tran currency]]}[Exchange Rate]?
.

BTW I recentely did a video on this type of data extraction, you can find that here

1 Like
3

Thank you @Melissa !

It worked!

Seen this video recently when you added :slight_smile: I acctually applied this formula to extract the data from the emails.

Once again thank you!

You are the Master of the M-code!

1 Like
4

My boss, today, “what’re you going to do about that payroll data that is that nasty format”

“It’s ok I’ve got that pulled in already”

“how’d you manage that?”

Showed him how I transformed it and explained a lot of the M code/Power Query skills I’ve picked up recently have been through @Melissa’s videos. I’m loving using Drilldown in Power Query and then bringing all the rows back again, so useful.

2 Likes
5

That’s just amazing @DavieJoe :+1:

I know @BrianJ has got a video lined up (expected to be released to the YT Channel sometime next week) on that Group By, All Rows option. Can’t wait for that!

1 Like
6

Yeah, I was pleased. He followed that up with “you’re really getting into your M code now”. I’ve finally managed to show him something he didn’t already know how to do :rofl:! Thanks again!

Look forward to more wizardry from @BrianJ. I’ve used Group By/All Rows for a few different scenarios, will be a great watch I’m sure!

1 Like