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
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?
Solved! Go to Solution.
split by '_' (once, as far left as possible). That should give 2 columns,
then split the 2nd column in the same way
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!
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 !
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 !
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!
split by '_' (once, as far left as possible). That should give 2 columns,
then split the 2nd column in the same way
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
60 | |
23 | |
17 | |
12 |