Forum Discussion

DGPBi's avatar
DGPBi
Icon for Helper I rankHelper I
2 years ago
Solved

Splitter.SplitTextByAnyDelimiter

Hi everyone,

I have a sharepoint list not maintained by me but I need to use some data.
I have a column "Site Name" with sometimes values like CNWQ01/02 or CNSGH10/11/12/13/14/15.
I need to split in different rows this column to obtain as result :
CNWQ01
CNWQ02
CNSGH10
CNSGH11
etc ...
I used the "Split Column" function and it has generated this  : 

Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Site Name", Splitter.SplitTextByAnyDelimiter({"/","-"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Site Name")

which partially do the job correctly ... but when the new row is created it is only with 02 or 11 but I need to have a full name like CNSGH11.
Please, can you help me to build this query 

Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    and try this code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvYLdvcwNNA3NNQ3NNI3NNY3NNE3NFWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [string = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"string", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [
    a = Splitter.SplitTextByPositions({0,
Text.PositionOfAny([string],{"0".."9"},Occurrence.First)})([string]),
b=  Expression.Evaluate("{"& Text.Replace(a{1},"/",",")&"}"),
c = List.Transform(List.Transform(b, Text.From),(x)=> if Text.Length(x)=1 then a{0}&"0"&x else a{0}&x)][c]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

5 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    and try this code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvYLdvcwNNA3NNQ3NNI3NNY3NNE3NFWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [string = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"string", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [
    a = Splitter.SplitTextByPositions({0,
Text.PositionOfAny([string],{"0".."9"},Occurrence.First)})([string]),
b=  Expression.Evaluate("{"& Text.Replace(a{1},"/",",")&"}"),
c = List.Transform(List.Transform(b, Text.From),(x)=> if Text.Length(x)=1 then a{0}&"0"&x else a{0}&x)][c]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"
    • DGPBi's avatar
      DGPBi
      Icon for Helper I rankHelper I
      2 years ago

      Thank you very much ... it works like a charm 😉

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    pls try this

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvYLdvcwNNA3NNQ3NNI3NNY3NNE3NFWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [string = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"string", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [ txt = "0123456789",
a =Splitter.SplitTextByCharacterTransition((x)=>not Text.Contains(txt,x),(x)=>Text.Contains(txt,x))([string]),
b = List.Transform(a,(x)=>  
if Text.Length(x)=1 then a{0}& "0"&Text.Remove(x,"/") else  a{0}&Text.Remove(x,"/")),
c = List.RemoveFirstN(b,1)
]
[c]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

     

    • DGPBi's avatar
      DGPBi
      Icon for Helper I rankHelper I
      2 years ago

      Thanks this solution also works like a charm ... I choosed your second version but both of them works fine. Thank you very much for your great help