Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am attempting to recreate a SQL query I have into M format so I can turn it into a function within PowerBI, but i'm having a little trouble.
WITH RPL (PART, SUBPART, QUANTITY) AS ( SELECT ROOT.[Parent Part No], ROOT.[Component Part No], ROOT.[Qty Required Per Parent] FROM vAssemblies ROOT WHERE ROOT.[Parent Part No] = '52630103' UNION ALL SELECT CHILD.[Parent Part No], CHILD.[Component Part No], CHILD.[Qty Required Per Parent] FROM RPL PARENT, vAssemblies CHILD WHERE PARENT.SUBPART = CHILD.[Parent Part No] ) SELECT DISTINCT PART, SUBPART, QUANTITY FROM RPL ORDER BY PART, SUBPART, QUANTITY;
How would I express the above in M?
Regards
Lee Barratt
Solved! Go to Solution.
my way of doing this (probably others) is to
1. drop the SQL in as normal,
2. edit query,
3. right click table in lefthand pane in query editor,
4. select advanced editor. you will then see the m version of your SQL.
If your source data sits in the SQL-server, the fastest solution will be to use the SQL-code as it is like @Artefx has described.
But if it doesn't, you can check out my M-solution for it here: https://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
The query in your post is a recursive query, the syntax is simple and easily reading. Converting it to "M" code just make things more complicated. May I know why do you have to do this? Due to the limitation on my knowledge on "M", so far I have no idea on how to convert it. But if you have to do that, I think this video Power Query User Defined Functions & Recursive Functions would give you a start.
Can you be more specific on your scenario, as I have more expertise on SQL, maybe we can achieve your requirement just by SQL.
my way of doing this (probably others) is to
1. drop the SQL in as normal,
2. edit query,
3. right click table in lefthand pane in query editor,
4. select advanced editor. you will then see the m version of your SQL.
samdthompson,
Thanks for the reply.
The problem with your suggestion is that the SQL code is simply embedded within an M statement, and not converted:
let Source = Sql.Database("red-apps01\fmast", "FMAST10", [Query="WITH RPL (PART, SUBPART, QUANTITY) AS#(lf)(#(lf)SELECT ROOT.[Parent Part No], ROOT.[Component Part No], ROOT.[Qty Required Per Parent]#(lf) FROM vAssemblies ROOT#(lf) WHERE ROOT.[Parent Part No] = '536304'#(lf)UNION ALL#(lf) SELECT CHILD.[Parent Part No], CHILD.[Component Part No], CHILD.[Qty Required Per Parent]#(lf) FROM RPL PARENT, vAssemblies CHILD#(lf) WHERE PARENT.SUBPART = CHILD.[Parent Part No]#(lf))#(lf)SELECT DISTINCT PART, SUBPART, QUANTITY#(lf) FROM RPL#(lf) ORDER BY PART, SUBPART, QUANTITY;"]) in Source
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |