Create a new column by group

[Anonymous]

I have a dataframe like this

group	country	type	value
a	japan	AB	1000
a	japan - AC	AB	500
a	Mexico - AC	CD	630
a	USA	AB	1400
b	japan	CD	1000
b	japan - AC	CD	0
b	USA - AC	AB	0
b	USA- AC	CD	0
b	Mexico	AB	1500
a	USA - AC	CD	6000
a	USA- AC	AB	4000

And I would to create a new column removing AC in column country, because after I want sum value of Japan and Japan - AC, for example. The result would be like this

group	country	type	value	country  2
a	japan	AB	1000	japan
a	japan - AC	AB	500	japan
a	Mexico - AC	CD	630	Mexico
a	USA	AB	1400	USA
b	japan	CD	1000	japan
b	japan - AC	CD	0	japan
b	USA - AC	AB	0	USA
b	USA- AC	CD	0	USA
b	Mexico	AB	1500	Mexico
a	USA - AC	CD	6000	USA
a	USA- AC	AB	4000	USA

[AlienSx · Accepted solution]

    Table.AddColumn(
        Source, "Custom", 
        each Text.Trim(
            if Text.EndsWith([country], "- AC") 
            then Text.Range([country], 0, Text.Length([country]) - 4) 
            else [country]
        )
    )

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUcpKLEjMUwAyHJ2AhKGBgYFSrA6ylIKugqMzTN4USdo3tSIzOR8m7ewCJMyMEdKhwY5wU02g2pKQLQTrgFuIkEI2ECEHNA7FJSgy2LVAHAh3BLLbkUyDONwAVRLZJhOwXCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [group = _t, country = _t, #"type" = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"group", type text}, {"country", type text}, {"type", type text}, {"value", Int64.Type}}),
    #"Extracted country" = Table.TransformColumns(#"Changed Type", {"country", each Text.Trim(Text.BeforeDelimiter(_,"-"))})
in
    #"Extracted country"

[AlienSx · Accepted solution]

    Table.AddColumn(
        Source, "Custom", 
        each Text.Trim(
            if Text.EndsWith([country], "- AC") 
            then Text.Range([country], 0, Text.Length([country]) - 4) 
            else [country]
        )
    )