Forum Discussion

astrbac's avatar
astrbac
Frequent Visitor
7 years ago
Solved

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

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). Th...
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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]
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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's avatar
PattemManohar
Community Champion
7 years ago

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"