Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lutolfr
Frequent Visitor

Column Splitting

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? 

 

 

 

3 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

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

then split the 2nd column in the same way

View solution in original post

Shaurya
Memorable Member
Memorable Member

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:

 

Screenshot 2022-10-09 042904.jpg

 

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

View solution in original post

serpiva64
Super User
Super User

Hi,

you can:

- first duplicate your column

serpiva64_0-1665270332568.png

- then Split column by delimiter on the duplicate column

serpiva64_1-1665270399454.png

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

serpiva64_2-1665270465496.png

and you obtain this

serpiva64_3-1665270501531.png

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 !

 

 

 

View solution in original post

5 REPLIES 5
serpiva64
Super User
Super User

Hi,

you can:

- first duplicate your column

serpiva64_0-1665270332568.png

- then Split column by delimiter on the duplicate column

serpiva64_1-1665270399454.png

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

serpiva64_2-1665270465496.png

and you obtain this

serpiva64_3-1665270501531.png

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
Memorable Member
Memorable Member

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:

 

Screenshot 2022-10-09 042904.jpg

 

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

HotChilli
Super User
Super User

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

then split the 2nd column in the same way

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
Resolver II
Resolver II

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors