Neither M or DaX formulas recognised

[dyingod]

Hello!

I've just spent 2 hours searching for a solution in here & google. I failz. Please help! 🙂

I have 2 unrelated tables that shouldn't be related anyway.

One of them is simply querying a public API for latest exchange rates and only has a single row.

I'll represent it as RUB2GBP[XR]

(to be honest... this was like a list or record... and I'd love to keep it like a list or record... I say this or that coz because I don't recognise the icon, here: . If I can query whatever this is without transforming it into a table, that'd be AMAZING)

The other is a table in which I need to create a custom column to use that exchange rate from the other table.

VK is the other table. I'd like to create a custom column called [Cost] where:

= VK[Cost in Russian Rubles] * RUB2GBP[XR]

i don't know if this is because they're not related but whether I use:

 * MAX(RUB2GBP[XR])

* SUM(RUB2GBP[XR])

Nothing works. It just says "Expression.Error: The Name "X" wasn't recognised.

Can someone help?

 Table 1 - which queries public API for a single exchange rate (that I need to use)Table 2 - I need to create a custom column converting SPENT (in Russian Rubles) to GBPThis is one of my gazillion attempts to use either M or DaX and always ends with the "wasn't recognised)

PS.: this is not per se Power BI Power Query but I will also use this in there, the problem exists in Excel Power Query.

Thank you

Raf

[Greg_Deckler]

It looks to me that you are trying to use DAX expressions where you should be using Power Query "M" Code. You could import the data into your data model and then create the custom columns/measures using the DAX that you have. But you need to do that in your data model in Excel and not in Power Query editor. See this article:

https://community.powerbi.com/t5/Community-Blog/The-Languages-of-Power-BI/ba-p/69104

[dyingod]

Hello! Thanks for this.

I tried both M and DaX, same issue.

Is there another way I could import the exchange rate API, which is a tiny URL, directly into the custom column formula? Rather than creating it as a separate table/list/query in itself?

Here are some screenshots if this helps?latest attempt using MVK is the table I want to import exchange rate toRUB2GBP is the query with exchange rate

[v-chuncz-msft]

@dyingod,

You may combine the two queries in Advanced Editor, and add custom column as follows.

Record.Field(Source2, "RUB")

[dyingod]

Thanks v-chuncz-msft!

Is that the only way? seems a shame to screw up a nicely modelled table with a weird single-value column but yeah if that is what it takes then yes pls! 🙂

[v-chuncz-msft · Accepted solution]

@dyingod,

As mentioned above, you could convert the record into a table and then add to data model. That way, you may use DAX to add a calculated column.