help with splitting column by delimitor only if it is in the last 4 digits

[Anonymous]

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>

[v-alq-msft]

Hi, @Anonymous 

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

[v-alq-msft]

Hi, @Anonymous 

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.

[nandukrishnavs]

@Anonymous 

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 🙂

[Anonymous]

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?

[nandukrishnavs]

@Anonymous  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 🙂

[mahoneypat]

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

[Anonymous]

Hi,

Ive tried that and i keep getting Syntax Errors : Token Eof expected