2 
    Source data:

Market  Platform        Web sales $ Mobile sales $  Insured
FR      iPhone          1323        8709              Y
IT      iPad            12434       7657              N
FR      android         234         2352355           N
IT      android         12323       23434             Y

Is there a way to evaluate the sales of devices that are insured?

if List.Contains({"iPhone","iPad","iPod"},[Platform]) and ([Insured]="Y") then [Mobile sales] else "error"

Something to that extent, just not sure how to approach it

Improve this question
5
  • 1
    The proposed column looks reasonable to me other than being a mixed data type. Commented May 5, 2020 at 21:45
  • it errors out though, not sure how to include an "and" context correctly Commented May 5, 2020 at 21:47
  • What does the error say? Commented May 5, 2020 at 21:48
  • expects a 'then' Commented May 5, 2020 at 22:01
  • if List.Contains({"iPhone","iPad","iPod"},[Platform]) and ([Insured]="Y") then "" else "error") this code works but everything results as an error (output) Commented May 5, 2020 at 22:02

1 Answer 1

Reset to default
1

A direct answer to your question is 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SumUpSales = Table.AddColumn(Source, "Sales of insured devices", each if List.Contains({"iPhone","iPad","iPod"}, _[Platform]) and Text.Upper(_[Insured]) = "Y" then _[#"Mobile sales $"] else null, type number)
in
    SumUpSales

However, I would like to stress you few things.

First, it's better to convert values in [Insured] column to boolean first. That way you can catch errors before they corrupt your data without you noticing. My example doesn't do that, all it does is negating letter case in [Insured], since PowerM is case-sensitive language.

Second, you'd better use null rather than text value error. Then, you can set column type, and do some math with its values, such as summing them up. In case of mixed text and number values you will get an error in this and many other cases.

And last. It is probably better way to use a pivot table for visualizing data like this. You just need to add a column which groups all Apple (and/or other) devices together based on the same logic, but excluding [Insured]. Pivot tables are more flexible, and I personally like them very much.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.