Power Query text parsing and splitting into records

[Krishanu]

Hi,

I have the following scenario - data looks like this as exmaple below :

ID	Query
1	select * from emp
2	select * from dept UNION select * from hr
3	select * from Purchase JOIN Expense ON Purchase. key = Expense.key JOIN Money ON Expense.key1 = Money.key1

required outcome:

ID	Table
1	emp
2	dept
2	hr
3	Purchase
3	Expense
3	Money

tried with Text.BetweenDelimiters  and Table.SplitColumn , but not able to get this.
Please help.

[CNENFRNL]

Resort to embedded R or Python with regular expression,

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpOzUlNLlHQUkgrys9VSM0tUIrViVYywpBJSS0oUQj18/T3i8lDlckoAmsxxtASUFqUnJFYnKrg5e/pp+BaUZAK1BmT5+8Hl9FTyE6tVLCFyekBeTF5YNW++XlAGX8/ZClDoEqwOJijFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SN = _t, Query = _t]),
    #"Extracted table" = Table.TransformColumns(Source, {"Query", each let l = List.Select(Text.SplitAny(_, " #(lf)"), each _<>""), pos = List.PositionOfAny(l, {"FROM", "JOIN"}, Occurrence.All, Comparer.OrdinalIgnoreCase) in List.Accumulate(pos, {}, (s,c) => s&{l{c+1}})}),
    #"Expanded Query" = Table.ExpandListColumn(#"Extracted table", "Query")
in
    #"Expanded Query"