Forum Discussion

Krishanu's avatar
Krishanu
Icon for Helper I rankHelper I
5 years ago
Solved

Power 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 E...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    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"

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago

Resort to embedded R or Python with regular expression,

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago
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"