Referencing a column by using a value in a different query

gjohal

New member
Joined
Nov 8, 2018
Messages
15
Reaction score
0
Points
0
Excel Version(s)
2016
Hello,

I have the following formula for creating a new column called "Rounding" in the PQ: = Table.AddColumn(#"Filtered Rows", "Rounding", each if [2018] < 15000 then 1 else if [2018] > 10000000 then 2 else 3)

I use this to create a new column that is filled based on the values of column "2018". This works great. However I wanted to replace the hard coded reference to column "2018" to a dynamic value. I want to be able to enter a value in the Excel workbook and have the "Rounding" column in the PQ calculate its value based on that. For example, If I enter 2017 in excel cell the "Rounding" column would be filled based on values from the "2017" column in the PQ.

I created another query based on a value in a cell in the workbook but I don't know how to refer to the value in this query as the heading of the column I want in the other query. My first thought was to replace [2018] in the formula above to a Table.FirstValue expression as such: = Table.AddColumn(#"Filtered Rows", "Rounding", each if Table.FirstValue(Year) < 15000 then 1 else if Table.FirstValue(Year) > 10000000 then 2 else 3) with Year being the query I created that links to the workbook. This didn't work though as it didn't take the value in the Year query and use it to find the column of the same name.

I don't know if I explained this well enough but hopefully someone can help?

Thanks!
 
Yes it was thanks!
 
[visiting old threads for practice]
Instead of:
Code:
= Table.AddColumn(#"Filtered Rows", "Rounding", each if [2018] < 15000 then 1 else if  [2018] > 10000000 then 2 else 3)
you can have:
Code:
= Table.AddColumn(#"Filtered Rows", "Rounding", each if Record.Field(_ ,"2018") < 15000 then 1 else if Record.Field(_ ,"2018")> 10000000 then 2 else 3)
now you just need to replace the literal strings "2018" with the other query name:
Code:
= Table.AddColumn(#"Filtered Rows", "Rounding", each if  Record.Field(_ ,TheYear) < 15000 then 1 else if Record.Field(_  ,TheYear)> 10000000 then 2 else 3)
 
Back
Top