Forum Discussion
Krishanu
Helper I
Helper IPower Query text parsing and splitting into records
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.
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"
2 Replies
CNENFRNLCommunity Champion
Resort to embedded R or Python with regular expression,
- CNENFRNL
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"