cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyingod Frequent Visitor
Frequent Visitor

Neither M or DaX formulas recognised

Hello!

 

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

 

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: icon.PNG. 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?

 table1.PNGTable 1 - which queries public API for a single exchange rate (that I need to use)table2.PNGTable 2 - I need to create a custom column converting SPENT (in Russian Rubles) to GBPcustom-column.PNGThis 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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Neither M or DaX formulas recognised

@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.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: Neither M or DaX formulas recognised

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
dyingod Frequent Visitor
Frequent Visitor

Re: Neither M or DaX formulas recognised

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?formula.PNGlatest attempt using Mexample2.PNGVK is the table I want to import exchange rate toexample1.PNGRUB2GBP is the query with exchange rate

Community Support Team
Community Support Team

Re: Neither M or DaX formulas recognised

@dyingod,

 

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

Record.Field(Source2, "RUB")
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dyingod Frequent Visitor
Frequent Visitor

Re: Neither M or DaX formulas recognised

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! Smiley Happy

Community Support Team
Community Support Team

Re: Neither M or DaX formulas recognised

@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.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.