Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Resort to embedded R or Python with regular expression,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |