Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Table.AddColumn(
Source, "Custom",
each Text.Trim(
if Text.EndsWith([country], "- AC")
then Text.Range([country], 0, Text.Length([country]) - 4)
else [country]
)
)