Latest Enterprise DNA Initiatives

M Code - two criteria for look up

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

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

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

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

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

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