Forum Discussion
IF OR Formula in Power Query for each row
Hi There,
I need to replicate below screen shot formula "OBSOLETE QTY" in Power Query. But I'm stuck because my power query is not calculating the 3 columns ....
I tried in power query as:
if [Sales Status] ="S6" or [Sales Status] = "S5", then [#"Consolidated Qty Stock 7-12"] + [#"Consolidated Qty Stock 12+"] + [Consolidated Qty Inactive]
else 0
Hi RDillon-2025 ,
When you say it's not calculating the three columns, what exactly do you mean? Are you getting an error, just a null value or what?
Based on what I can see, there may be a couple of issues:
1) You have a comma in your code, which will give you an error.
2) If your table in Power Query actually has nulls instead of zeroes, the addition you're doing will evaluate to null.
Try using this code instead:
if [Sales Status] = "S6" or [Sales Status] = "S5" then List.Sum({[#"Consolidated Qty Stock 7-12"], [#"Consolidated Qty Stock 12+"], [#"Consolidated Qty Inactive"]}) else 0Pete
3 Replies
Hi RDillon-2025 ,
When you say it's not calculating the three columns, what exactly do you mean? Are you getting an error, just a null value or what?
Based on what I can see, there may be a couple of issues:
1) You have a comma in your code, which will give you an error.
2) If your table in Power Query actually has nulls instead of zeroes, the addition you're doing will evaluate to null.
Try using this code instead:
if [Sales Status] = "S6" or [Sales Status] = "S5" then List.Sum({[#"Consolidated Qty Stock 7-12"], [#"Consolidated Qty Stock 12+"], [#"Consolidated Qty Inactive"]}) else 0Pete
Pete Thank you !!!!!!!!! it works perfectly well OMG I missed out the list.sum as initially I added and then I removed the reason I was getting zero results it thank you heaps I'll note for all future!!!! I have one more will post a different query.
No problem, happy to help.
Don't forget to give a thumbs-up on any posts that have helped you 👍Pete
