Results 1 to 6 of 6

Thread: Referencing a column by using a value in a different query

  1. #1
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016

    Exclamation Referencing a column by using a value in a different query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    112
    Articles
    0
    Excel Version
    365
    Hi,
    Your problem is complex, you can use this solution.
    Hope that it will help you.

    solution to gjohal.xlsx



    To learn more, visit my personal blog http://numidiabi.wordpress.com

  3. #3
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016
    Thank you!

  4. #4
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    112
    Articles
    0
    Excel Version
    365
    is what you were looking for ?

  5. #5
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016
    Yes it was thanks!

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,734
    Articles
    0
    Excel Version
    365
    [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)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •