Generate new Column

[nicoledavis]

Hi Everyone,

How do i make a new custom column to show the relevant Sales Manager to their Area Code? i.e Area: 4011 = Jack 

Area 4012 = Nura

[Mahesh0016]

@nicoledavis 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTbRSMDEwNFSK1UHiGinFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AREA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AREA", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sales Manager", each if[AREA]="Area: 4011" then "Jack" else "Nura")
in
    #"Added Custom"

 @nicoledavis I hope this helps you!Thank You!! 

[nicoledavis]

it's actually a little more involved as theres a lot of areas and the Sales Managers have a few allocated to them, there are also 4 Sales Managers.

if you can help please

Jon = Areas 4011, 4015 and 4020

Jack = 4012

Lydia = 4013 and 4014

Nura = 4016 and 4021

there are a few other areas that do not have a Sales Manager 

thank you 🙂

[Mahesh0016]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTbRSMDEwNFSK1UHiGqFyjVG5JqhcU1SuGSrXHJVrgcq1ROEaGaByga6KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AREA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AREA", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sales Manager", each if[AREA]="Area: 4011" then "Jack" else "Nura"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sales Managers", each 
                        if [AREA] = "Area: 4011" or [AREA]="Area: 4015" or [AREA]="Area: 4020" then "Jon" 
                        else if [AREA] = "Area: 4012" then "Jack"
                        else if [AREA] = "Area: 4013" or [AREA]="Area: 4014" then "Lydia"
                        else if [AREA] = "Area: 4016" or [AREA]="Area: 4021" then "Nura"
                        else "do not have a Sales Manager")
in
    #"Added Custom1"

[nicoledavis]

thank you, however when i select custom column, the column just displays "Table" 

[Mahesh0016]

@nicoledavis 
I think you Copy-Paste all M code, you need only copy - Paste custom column code
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Sales Managers", each
if [AREA] = "Area: 4011" or [AREA]="Area: 4015" or [AREA]="Area: 4020" then "Jon"
else if [AREA] = "Area: 4012" then "Jack"
else if [AREA] = "Area: 4013" or [AREA]="Area: 4014" then "Lydia"
else if [AREA] = "Area: 4016" or [AREA]="Area: 4021" then "Nura"
else "do not have a Sales Manager")
in
#"Added Custom1"