Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom Column - Multiply value by a value in another table

Hello

 

There are DAX solutions to this, but I'm quite sure it's also possible in M

 

I want to add a Custom Column and multiply a value in the existing table(query) by a value in another table(query). When wriing my code I can add the table name, but I can't seem to refrence the columns from that table.

 

Below is the code I'm trying to run. I can get the #"GBP - USD/EUR" table but i want to refrence the [Date] and [EUR] columns from that table

 

if [Currency] = "EUR" then [Target Costs] else 
if [Currency] = "GBP" and [Date] = #"GBP - USD/EUR [Date]" then [Target travel Costs] * #"GBP - USD/EUR [EUR]" else [Target Costs]

 Thanks in advance

Need Help
  • vojtechsima's avatar
    vojtechsima
    4 years ago

    Hi, Anonymous 
    I did some testing, I rewrote your code into functioning Power Query M code (Add column), hope I get your request:

    let
currency = [Currency], targetcost = [Target Costs], position = List.PositionOf(#"GBP - USD/EUR"[Date], [Date]),
euro = #"GBP - USD/EUR"[EUR]{position}


in
if currency = "EUR" then targetcost else 
if currency = "GBP" and position >=0  then targetcost * euro else targetcost

    I assume that the GBP - USD/EUR table contains the exchange rate and you are looking for a specific date in the second table to get the correct Rate.

3 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    Hi, Anonymous 
    I did some testing, I rewrote your code into functioning Power Query M code (Add column), hope I get your request:

    let
currency = [Currency], targetcost = [Target Costs], position = List.PositionOf(#"GBP - USD/EUR"[Date], [Date]),
euro = #"GBP - USD/EUR"[EUR]{position}


in
if currency = "EUR" then targetcost else 
if currency = "GBP" and position >=0  then targetcost * euro else targetcost

    I assume that the GBP - USD/EUR table contains the exchange rate and you are looking for a specific date in the second table to get the correct Rate.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you!

      • vojtechsima's avatar
        vojtechsima
        Super User
        4 years ago

        Hi, Anonymous 
        If you could mark my answer as the solution please if it helped you.