Forum Discussion

saturated_snail's avatar
saturated_snail
Frequent Visitor
4 years ago
Solved

Splitting a string into all possible substrings of certain length

I have a column, [Description], with text strings of varying lengths.   eg. DET.ASS.RR+1005177   I want to get a list of every 7 character long string contained within this string.   Exp...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnEN0XMMDtYLCtI2NDAwNTQ3V4rViVZKTEpOSU1Lz8jMygbzDY2MTUzNzC2QOUhspdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
       #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Generate(()=>[x=Text.Middle([Data],0,7),d=[Data],i=0], each [i]<Text.Length([d])-6, each [i=[i]+1,d=[d], x=Text.Middle([d],i,7)], each [x])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

     

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnEN0XMMDtYLCtI2NDAwNTQ3V4rViVZKTEpOSU1Lz8jMygbzDY2MTUzNzC2QOUhspdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
       #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Generate(()=>[x=Text.Middle([Data],0,7),d=[Data],i=0], each [i]<Text.Length([d])-6, each [i=[i]+1,d=[d], x=Text.Middle([d],i,7)], each [x])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"