Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Pass Parameter as column name in custom column calculation

Example Table:

OriginDestinationScenario 1Scenario 2Scenario 3
AB101112
BC63

2

 

Thank you in advance for helping me solve this problem! 

 

I have 2 parameters that define which 2 fields I want to use in a calculated new column. For example: A user selects Scenario 2 and Scenario 3 as Parameter 1 and Parameter 2, respectively. In a new calculated column I would see the results of Scenario 2 value - Scenario 3 value, as shown in the Calc Col below. What does the syntax look like to create this calculated column based on the user selected parameters?

 

OriginDestinationScenario 1Scenario 2Scenario 3Calc Col
AB101112-1
BC6321
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    u could, peraphs use this sheme:

     

    a function like this:

     

    let
    newTab = (Col1,Col2)=>Table.AddColumn(Table, "newColumnName", each Record.Field(_, Col1)-Record.Field(_, Col2))

in
    newTab

     

    which when invoked using the names of two columns of you table:

     

    gives:

     

     

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    I think i figured it out. I modified your Function to be a simple addcolumn step:

     

    Table.AddColumn(PreviousStep, "Delta", each Record.Field(_, #"Scenario 1")-Record.Field(_, #"Scenario 2"))

     

    Where Scenario 1 and Scenario 2 are the query parameter names. Seems to work! 

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    u could, peraphs use this sheme:

     

    a function like this:

     

    let
    newTab = (Col1,Col2)=>Table.AddColumn(Table, "newColumnName", each Record.Field(_, Col1)-Record.Field(_, Col2))

in
    newTab

     

    which when invoked using the names of two columns of you table:

     

    gives:

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Close! The only issue I am running into is the parameters are query parameters so I am not sure how to assign the query parameters to your function parameters? If that makes sense?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I think i figured it out. I modified your Function to be a simple addcolumn step:

       

      Table.AddColumn(PreviousStep, "Delta", each Record.Field(_, #"Scenario 1")-Record.Field(_, #"Scenario 2"))

       

      Where Scenario 1 and Scenario 2 are the query parameter names. Seems to work!