Forum Discussion

AVGUser_PBI's avatar
AVGUser_PBI
Helper I
2 years ago
Solved

Create a custom column by adding two conditions

Hello everyone!

 

below is a table I have:

 

 

I am trying to add custom columns that shows values as shown below in yellow:

 

I am trying power query as shown below: custom column :

each(

    (each if([effective_date] < #datetime(2023,1,1,0,0,0) and [attribute] = "recorded") then [Value] else 0)

    +

    (each if([capture_date] = #datetime(2023,1,1,0,0,0) and [attribute] = "estimated") then [Value] else 0)

         )

 

I received error:

Expression.Error: We cannot apply operator + to types Function and Function.
Details:
Operator=+
Left=[Function]
Right=[Function]

 

Please help me figure out what am I doing wrong. OR how to achieve desired output as shown above in yellow columns. 

 

thank you,

 

note: effective_date and capture_date are in date-time format

 

 

  • AVGUser_PBI  Try this to see if it works

     

    each(

    (each if([effective_date] < #datetime(2023,1,1,0,0,0) and [attribute] = "recorded") then [Value] else 0)

    +

    (each if([capture_date] = #datetime(2023,1,1,0,0,0) and [attribute] = "estimated") then [Value] else 0)

    )

2 Replies

  • smpa01's avatar
    smpa01
    Community Champion

    AVGUser_PBI  Try this to see if it works

     

    each(

    (each if([effective_date] < #datetime(2023,1,1,0,0,0) and [attribute] = "recorded") then [Value] else 0)

    +

    (each if([capture_date] = #datetime(2023,1,1,0,0,0) and [attribute] = "estimated") then [Value] else 0)

    )

    • AVGUser_PBI's avatar
      AVGUser_PBI
      Helper I

      Yes it worked! 

       

      do you know what was I doing  by keeping  'each if' in the query?