Forum Discussion
Custom column pulling from different tables
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
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
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
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