Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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! |
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |