Forum Discussion
Extract all text between two strings if a value is present?
Hello!
I'm looking to extract text with M in a column if a speciphic value is available between two constant strings.
In this example:
The cell contents of the data column are always the same.
"xxxx" is a constant available always at the beginning of the text
"aaaa" is a constant available in several places in the text, there is always a single ID between two "aaaa"
there may be "aaaa" without another "aaaa" after, as in the case of the 3rd line
Possible ?
Thanks!
Try this one, hard to tell with the small sample size but it seems a bit better.
let
Source =YOUR SOURCE,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"data", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"data", Splitter.SplitTextByDelimiter("aaaa", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "data"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"data", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "outputData", each if Text.Contains([data], Text.From([ID])) then [data] else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([outputData] <> null)),
#"Added Prefix" = Table.TransformColumns(#"Filtered Rows", {{"outputData", each "aaaa" & _, type text}})
in
#"Added Prefix"
4 Replies
Based on your example I can get
using the following
let
Source = PUT YOUR SOURCE HERE,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"data", type text}}),
#"create dataLists" = Table.AddColumn(#"Changed Type", "dataList", each Text.Split([data],"aaaa")),
#"expand dataLists" = Table.ExpandListColumn(#"create dataLists", "dataList"),
#"test for ID" = Table.AddColumn(#"expand dataLists", "containsID", each Text.Contains([dataList],Text.From([ID]))),
#"Filtered Rows" = Table.SelectRows(#"test for ID", each ([containsID] = true)),
#"append dataLists" = Table.AddColumn(#"Filtered Rows", "outputData", each "aaaa" & [dataList]),
#"Removed Columns" = Table.RemoveColumns(#"append dataLists",{"dataList", "containsID"})
in
#"Removed Columns"The basic idea was to create a list for each data cell by splitting the data by "aaaa". Then expand the lists into rows and test each row for the ID number. Filter that result to only keep rows that have the ID in them. I then appended that result with "aaaa" and cleaned up the un-needed columns.
Hope this gets you going in the right direction.
Try this one, hard to tell with the small sample size but it seems a bit better.
let
Source =YOUR SOURCE,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"data", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"data", Splitter.SplitTextByDelimiter("aaaa", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "data"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"data", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "outputData", each if Text.Contains([data], Text.From([ID])) then [data] else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([outputData] <> null)),
#"Added Prefix" = Table.TransformColumns(#"Filtered Rows", {{"outputData", each "aaaa" & _, type text}})
in
#"Added Prefix"