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

[jk8979356]

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. 

[AnkitKukreja]

Hi @jk8979356 

Please try this, you should get that column from your forecast table to your current table and then you can use it in your formula.

Please refer this.

https://community.powerbi.com/t5/Desktop/M-Script-Help-custom-column-referencing-another-table/td-p/...

Thanks,

Ankit

[jk8979356]

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?

[CNENFRNL]

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]

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?

[CNENFRNL]

@jk8979356 , what's the name of the query where you extract the number?

[jk8979356]

The query name for I extract the number is "2020forecast"

[CNENFRNL · Accepted solution]

@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])))

[jk8979356]

That worked. You are awesome! Thank you so much for your help!