Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Im having issues splitting a column using the . Delimitor, What i have is a column that has Filename and Extensions in one but not all the rows have a filename. So here is the problem when i split using the . delimitor to try get a column with just extentions i also get some data where people may have used a "." in the filepath. What i want to do is split using the "." only if it is in the last 4 digits?
Here is code sofar:
<Code>
= Table.SplitColumn(#"Changed Type", "Column4", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.None, true), {"Column4.1", "Column4.2"})
</Code>
Hi, @swys42
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Hi, @swys42
Based on your description, I created data to reproduce your scenario.
Table:
You may go to 'Query Editor'=>"Transform"=>"Split Column"=>"By Delimiter", set as below.
Result:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTTXUSy4uU4rVAfH0ULm5qXpgFUbGJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom.2", each let lengthofText=Text.Length([Column1]),
postionOfdot=Text.PositionOf([Column1], ".", Occurrence.Last),
a= lengthofText-postionOfdot,
b= if a<=4 then Text.Middle([Column1], postionOfdot, lengthofText) else ""
in b)
in
#"Added Custom2"
You can use below PQ in the custom column section
let
lengthofText = Text.Length([Column1]),
postionOfdot = Text.PositionOf([Column1], ".", Occurrence.Last),
a = lengthofText - postionOfdot,
b = if a <= 4 then Text.Middle([Column1], postionOfdot, lengthofText) else ""
in
b
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Thanks i tried this and it does work, but realised that i have some extensions that is longer than the 4 characters and getting and error.
Any ideas how to do all extentions after the . right dot but not have the 4 character limit?
@swys42 Replace the 4 with expected maximum value. Or use split column ==>By Delimiter ==> select custom(enter dot) and choose Right-most delimiter.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Here is one way to do it. Duplicate your filename column, then use text before and text after delimiter with advanced option of From the End of the Input. Here is an example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTTXUSy4uU4rVAfH04NxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Column1", each Text.BeforeDelimiter(_, ".", {0, RelativePosition.FromEnd}), type text}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Column1 - Copy", each Text.AfterDelimiter(_, ".", {0, RelativePosition.FromEnd}), type text}})
in
#"Extracted Text After Delimiter"
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi,
Ive tried that and i keep getting Syntax Errors : Token Eof expected
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |