Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Custom column pulling from different tables

I have a table 2019 and a table 2020. I want to create a custom column for if the rows have the same pointers and durations, then take the 2020 rate and subtract the 2019 rate.

 

This is the formula I used:

if(and(2020[pointer]=2019[pointer], 2020[duration]=2019[duration]), 2020[rate]-2019[rate],null)

           ^^^

I get an error Token Literal Expected under the 2020. What is the proper way to code this?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous,

    My formula is DAX calculated column formula, please create it in a calculated column instead of using it in power query side.

    For power query custom column, it should more complex that dax formula, you need to use 'table.selectrow' with other query table name to filter records and calculate the result with current row field value.

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [rate]- Function.Invoke((_pointer,_duration)=>List.Sum(Table.SelectRows(#'2019', each [pointer]=_pointer and [duration]=_duration)[rate]),{[pointer],[duration]}))

    Regards,

    Xiaoxin Sheng

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    conclude table names in your statemen into ordinar quotas ' like

    if(and('2020'[pointer]='2019'[pointer], '2020'[duration]='2019'[duration]), '2020'[rate]-'2019'[rate],null)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

    You can't direct invoke another table column in the current table, please use an aggregate function with filters to filter search table records. 

    Calculate column: add to '2019' table

    Rate Diff =
VAR rate =
    CALCULATE (
        MAX ( '2020'[Rate] ),
        FILTER (
            ALLSELECTED ( '2020' ),
            '2020'[Pointer] = EARLIER ( '2019'[Pointer] )
                && '2020'[Duration] = EARLIER ( '2019'[Duration] )
        )
    )
RETURN
    rate - '2019'[Rate]

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I did this and I now have the error "Token Eof expected" on the "RETURN". I tried changing it to lower case because I know that power query is case sensitive but the error remained the same. Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous,

        My formula is DAX calculated column formula, please create it in a calculated column instead of using it in power query side.

        For power query custom column, it should more complex that dax formula, you need to use 'table.selectrow' with other query table name to filter records and calculate the result with current row field value.

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [rate]- Function.Invoke((_pointer,_duration)=>List.Sum(Table.SelectRows(#'2019', each [pointer]=_pointer and [duration]=_duration)[rate]),{[pointer],[duration]}))

        Regards,

        Xiaoxin Sheng