Forum Discussion

nicoledavis's avatar
nicoledavis
New Member
3 years ago

Generate new Column

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

 

Need Help

5 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    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's avatar
      nicoledavis
      New Member
      3 years ago

      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's avatar
        Mahesh0016
        Super User
        3 years ago
        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"