Forum Discussion
Create a new column by group
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 |
Table.AddColumn( Source, "Custom", each Text.Trim( if Text.EndsWith([country], "- AC") then Text.Range([country], 0, Text.Length([country]) - 4) else [country] ) )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"
2 Replies
Table.AddColumn( Source, "Custom", each Text.Trim( if Text.EndsWith([country], "- AC") then Text.Range([country], 0, Text.Length([country]) - 4) else [country] ) )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"