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

az38's avatar
az38
Icon for Community Champion rankCommunity 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)