topic Re: Column Splitting in Power Query

Column Splitting

lutolfr — Sat, 08 Oct 2022 22:24:28 GMT

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?

Re: Column Splitting

jgordon11 — Sat, 08 Oct 2022 22:55:54 GMT

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

Re: Column Splitting

HotChilli — Sat, 08 Oct 2022 22:58:54 GMT

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

then split the 2nd column in the same way

Re: Column Splitting

Shaurya — Sat, 08 Oct 2022 23:02:33 GMT

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!

Re: Column Splitting

serpiva64 — Sat, 08 Oct 2022 23:10:16 GMT

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 !

Re: Column Splitting

lutolfr — Sun, 09 Oct 2022 16:45:55 GMT

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.