Forum Discussion

jk8979356's avatar
jk8979356
Regular Visitor
5 years ago
Solved

How do I reference a specific cell value from a 2nd table inside of a formula on a different table?

I have a table that has the following formula:

 

= Table.AddColumn(#"Changed Type2", "Qty Forecast", each if [Quantity] <= 0 then [Quantity] else ([Quantity] / (List.Sum(#"Changed Type2"[Amount]) / 19950000)))

 

This formula works, but rather than have a hard coded value of 1995000, I want to use a variable value that is pulled from another table in the Power Query Editor. 

 

The 2nd table is named "forecast" and has the value in row 1 of column2.

 

What is the exact M script code to replace the hardcoded 19950000 value with a script that pulls the value from the "forecast" table, row 1, column2? Both tables are in the same Power Query Editor but are not merged queries. 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    jk8979356 , so you may use

     

    = Table.AddColumn(#"Changed Type2", "Qty Forecast", each if [Quantity] <= 0 then [Quantity] else ([Quantity] / (List.Sum(#"Changed Type2"[Amount]) / #"2020forecast"{0}[column2])))

     

8 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, jk8979356 , you may use 

    = Table.AddColumn(#"Changed Type2", "Qty Forecast", each if [Quantity] <= 0 then [Quantity] else ([Quantity] / (List.Sum(#"Changed Type2"[Amount]) / forecase[column2]{0})))

    or equally

    = Table.AddColumn(#"Changed Type2", "Qty Forecast", each if [Quantity] <= 0 then [Quantity] else ([Quantity] / (List.Sum(#"Changed Type2"[Amount]) / forecase{0}[column2])))
    • jk8979356's avatar
      jk8979356
      Regular Visitor
      5 years ago

      Thanks for the help. When I add that I get the following error:

       

      If I open it in the Advanced Editor, it doesn't show the full error:

      Any ideas?

       

    • jk8979356's avatar
      jk8979356
      Regular Visitor
      5 years ago

      I have read that discussion, but don't understand the exact M code I need in my situation. I'm fine if I need to first bring in the value from the forecast table into the sales table, but what exact M code do I use to do this? Since there is only a single value in the forecast table and I am not using a match of any kind, but rather using 1 single value for all rows, what code do I use to add the forecast value into my sales table as a new column?