Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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+1005177 | List |
I have tried List.Generate but cannot get this to work with text.
Thanks
Jamie
Solved! Go to Solution.
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"
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"