Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
saturated_snail
Frequent Visitor

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.

 

Expected result =

DET.ASS
ET.ASS.
T.ASS.R
.ASS.RR
ASS.RR+
SS.RR+1
S.RR+10
.RR+100
RR+1005
R+10051
+100517
1005177

 

Naturally my final table would end up with a list of descriptions with a list column:

DET.ASS.RR+1005177List

 

I have tried List.Generate but cannot get this to work with text.

 

Thanks

 

Jamie

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

 

 

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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"

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors