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 formul...
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

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