Add Custom column based on string in another column (if device = "iPhone" or "iPad" then do this)?

[astrbac]

Hi all!

I am just getting started with Power Query and "Get and transform" in general but have some previous understanding of more advanced Excel features (such as pivot tables, VBA and such). The problem I have is following:

Source data:

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

Output report needed:

Market	Total sales $
FR	(Web sales + Mobile sales)
IT	(Web sales + Mobile sales)

However, because of the data collection error that I cannot fix right now, I need to select all the rows where [Platform] equals "iPhone", "iPad" or "iPod" and divide the values of [Mobile sales $] field by /2 before adding to [Web Sales $].

What I tried:

IF [Impression Device]="iPhone" OR "iPad" OR "iPod"
THEN
[Web sales conversion value]+([Mobile app purchases conversion value]/2)
ELSE
[Web sales conversion value]+[Mobile app purchases conversion value]

This however does not work, for a multitude of reasons, I suspect.

1.  Function naming - in my case: IF works, if does not work, returns: "Token literal expected" error (on the internet it's vice-versa);
2. As soon as I get to this part:

   IF [Impression Device]="iPhone"

   I get a "Token eof expected" error;
3. I'm most probably using the round bracket in a wrong way;

Any help will be most appreciated!

Alex

[astrbac]

Oh... I see this will not be as simple as I thought it would. This might be a bit over my head, as it looks like C# or some similar language, with which I have no experience.

I thank you all for the effort and your time but I don't know which reply to accept. I cannot use this because I don't fully understand it. All of the pivots that would be build on top of this might be incorrect i make some errors with other connections and/or when refreshing the data.

Thanks all once again!

Alex

[PattemManohar]

@astrbac This is "M Query" Language, This will be generated automatically for all the steps that will be perfomed in Power Query.  (For advanced functionality, one can write the code directly in M code as well). For sharing the solution (Power Query) this is the simple way of sharing the steps performed. You simply paste this code in the "Advanced Editor" which will then show the all the steps that was involved in this on right side under "Applied Steps". 

Please try to make your hands dirty with "Power Query" then you can understand it better....

[Greg_Deckler]

Is this what you are looking for?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgtS0lHKDMjIz0sFMgyNjYyBlIW5gaVSrE60kmcIWDYxBSRnZGJsAqTNzUzNwZJgrYl5KUX5mSB5I7CskbEpEJkidCMUGBpBTAcqBCqNjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Market = _t, Platform = _t, #"Web sales $" = _t, #"Mobile sales $" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Platform", type text}, {"Web sales $", Int64.Type}, {"Mobile sales $", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Adjusted Mobile sales $", each if [Platform] = "iPhone" or [Platform]="iPad" or [Platform]="iPod" then [#"Mobile sales $"]/2 else [#"Mobile sales $"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Mobile sales $"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Adjusted Mobile sales $", "Mobile sales $"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Sales $", each [#"Web sales $"]+[#"Mobile sales $"]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Platform", "Web sales $", "Mobile sales $"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Market"}, {{"Total Sales $", each List.Sum([#"Sales $"]), type number}})
in
    #"Grouped Rows"

[PattemManohar]

@astrbac Here is the steps I've followed to solve this scenario..

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgtS0lHKDMjIz0sFMgyNjYyBlIW5gaVSrE60kmcIWDYxBSRnZGJsAqTNzUzNwZJgrYl5KUX5mSB5I7CskbEpEJkidCMUGBpBTAcqBCqNjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Market = _t, Platform = _t, WebSales = _t, MobileSales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Platform", type text}, {"WebSales", Int64.Type}, {"MobileSales", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MobileSalesNew", each if List.Contains({"iPhone","iPad","iPod"},[Platform]) then [MobileSales]/2 else [MobileSales]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "TotalSales", each [WebSales] + [MobileSalesNew]),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Market"}, {{"TotalSales", each List.Sum([TotalSales]), type number}})
in
    #"Grouped Rows"

[LivioLanzo]

Try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgtS0lHKDMjIz0sFMgyNjYyBlIW5gaVSrE60kmcIWDYxBSRnZGJsAqTNzUzNwZJgrYl5KUX5mSB5I7CskbEpEJkidCMUGBpBTAcqBCqNjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Market = _t, Platform = _t, #"Web sales $" = _t, #"Mobile sales $" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Platform", type text}, {"Web sales $", Int64.Type}, {"Mobile sales $", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Mobile Sales", each if List.Contains( {"iPod", "iPad", "iPhone" }, [Platform] ) then [#"Mobile sales $"] / 2 else [#"Mobile sales $"], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Mobile sales $"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Market", "Platform"}, "Category", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Market"}, {{"TotalSales", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

[astrbac]

@PattemManohar 

A-HA! 🙂 so that is it... this works sort of like recording a Macro and then just the "raw" M language is copied and shared. 

In that case, how do I use this? This is what I did:

1. I opened my document, clicked the Query name in the right side panel;
2. I chose Query > Edit;
3. In the Power query Editor I selected the "Add custom column";
4. Pasted @Greg_Deckler code into it and added the column;

This did produce the small table with FR and IT results. However, in mz initial post I simpliffied the problem mz creating this simple dataset. The real data that I have is some 20 fields and 15.000 records. Greg's code works for the example but not for my real life case.  

Generically, this is what I need: 

• create custom field (called "Corrected Total sales");
• by adding two already existing fields ("Website conversion value" and "Mobile app conversions value");
• based on the values in a third field (if "Impression device" equals {iPhone, iPad, iPod}... then divide /2, otherwise add normally);

This is what was already in there in the steps I managed to do myself:

let
    Source = Csv.Document(File.Contents("C:\Users\Ed Chigliak\Google Drive\XXXXX\XXXXXXX\reports\raw-data\fb\XXXXXXXXXX-FR-IT-ES-XXXXXXX-Ad-sets-1-May-201814-October-2018.csv"),[Delimiter=",", Columns=28, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Reporting starts", type date}, {"Reporting ends", type date}, {"Ad set name", type text}, {"Platform", type text}, {"Placement", type text}, {"Device platform", type text}, {"Impression Device", type text}, {"Campaign name", type text}, {"Delivery", type text}, {"Date created", type date}, {"Budget", Int64.Type}, {"Budget Type", type text}, {"Amount spent (GBP)", type number}, {"Website conversion value", type text}, {"Mobile app purchases conversion value", type number}, {"Website conversions", Int64.Type}, {"Mobile app purchases", Int64.Type}, {"Website purchase ROAS (return on advertising spend)", type text}, {"Mobile app purchase ROAS (return on advertising spend)", type number}, {"Results", Int64.Type}, {"Result indicator", type text}, {"Cost per results", type number}, {"Reach", Int64.Type}, {"Impressions", Int64.Type}, {"Link clicks", Int64.Type}, {"Landing page views", Int64.Type}, {"Ends", type text}, {"Starts", type date}})
in
    #"Changed Type"

Cheers!

[PattemManohar · Accepted solution]

@astrbac If you just looking for the new column "Correct Total Sales" logic, then please add "Custom Column" in Power Query as below:

if List.Contains({"iPhone","iPad","iPod"},[Platform]) then ([MobileSales]/2) + [WebSales] else [MobileSales] + [WebSales]

[astrbac]

@PattemManohar @Greg_Deckler @LivioLanzo

Pattem, this worked! 🙂 Now, I don't know whose solution I should accept, since all of you helped out but I didn't explain well in the beginning what I needed.

Any advice here?

[PattemManohar]

@astrbac Great !! It's totally upto you to accept the solution (It is for just quick reference for others to look into the solution directly instead of going-through entire chain). Also, there might be multiple solutions provided for the same scenario... So you can also accept multiple solutions as well... At the end of the day it's all about learning and sharing.....