Column Splitting

[lutolfr]

My column has data like

75_Bear_IMG_34567.jpg

76_Bobcat_08070203.jpg

77_Racoon_IMG_55555.jpg

I want to split into three columns. A location number, a specie and an image name. These three species are examples. Specie name could be 3 to 10 characters

75 Bear IMG_34567.jpg

76 Bobcat 08070203.jpg

77 Racoon IMG_55555.jpg

I see how to split the first two digits, but haven't found how to split the rest. I have tried using underscore as a custom delimiter but that hasn't worked. I have searched the docts and don't see how any of the 7 suggested methods apply. 

If I can delete the first 2 unscores, then I probably could use lower case to upper case and nondigit to digit. 

Is there a better way? 

[HotChilli · Accepted solution]

split by '_' (once, as far left as possible). That should give 2 columns,

then split the 2nd column in the same way

[Shaurya · Accepted solution]

Hi @lutolfr,

You can use list functions to convert the text into a list, for the location number and species, you can just read the first and second element of the list. For image name, you'll be choosing everything after the species name.

Here's how you can do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjeNd0pNLIr39HWPNzYxNTPXyypIV4rVAcqYxTvlJyUnlsQbWBiYGxgZGCOkzOODEpPz8/PA2kxBACIXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Location Number", each Text.Split([Values],"_"){0}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Species", each Text.Split([Values],"_"){1}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Image Name", each List.Range(Text.Split([Values],"_"),2)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Image Name", each Text.Combine(List.Transform(_, Text.From), "_"), type text})
in
    #"Extracted Values"

Result:

Works for you? Mark this post as a solution if it does!

[serpiva64 · Accepted solution]

Hi,

you can:

- first duplicate your column

- then Split column by delimiter on the duplicate column

an finally in Add column Extract text after delimiter on the original column

and you obtain this

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Shaurya · Accepted solution]

Hi @lutolfr,

You can use list functions to convert the text into a list, for the location number and species, you can just read the first and second element of the list. For image name, you'll be choosing everything after the species name.

Here's how you can do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjeNd0pNLIr39HWPNzYxNTPXyypIV4rVAcqYxTvlJyUnlsQbWBiYGxgZGCOkzOODEpPz8/PA2kxBACIXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Location Number", each Text.Split([Values],"_"){0}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Species", each Text.Split([Values],"_"){1}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Image Name", each List.Range(Text.Split([Values],"_"),2)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Image Name", each Text.Combine(List.Transform(_, Text.From), "_"), type text})
in
    #"Extracted Values"

Result:

Works for you? Mark this post as a solution if it does!

[HotChilli · Accepted solution]

split by '_' (once, as far left as possible). That should give 2 columns,

then split the 2nd column in the same way

[lutolfr]

I tried this solution first because fewer steps. I also tried serpiva64 and Shaurya and they worked. I also learned stuff from the others but didn't try to confirm that they worked. So thanks everyone.

[jgordon11]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
        "i45WMjeNd0pNLIr39HWPNzYxNTPXyypIV4rVAcqYxTvlJyUnlsQbWBiYGxgZGCOkzOODEpPz8/PA2kxBACIXCwA=", BinaryEncoding.Base64), Compression.Deflate))),
    lst = List.Transform(Source[Column1], each let t = Text.Split(_, "_") in List.FirstN(t,2) & {Text.Combine(List.Skip(t,2), "_")}),
    Result = Table.FromRows(lst, {"Location","Species", "Image Name"})
in
    Result