CODEWITHSUNDEEP
powerbidaxpowerquery
konsama
konsama

Reputation: 347

Add custom column based on string in another column

    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

Upvotes: 2

Views: 594

Answers (1)

Eugene
Eugene

Reputation: 1264

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.

Upvotes: 1

Related Questions