Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
@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 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)
)
Yes it worked!
do you know what was I doing by keeping 'each if' in the query?
User | Count |
---|---|
9 | |
8 | |
6 | |
6 | |
6 |