Forum Discussion
Converting from SQL to M
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
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.
5 Replies
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
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.
- I think this post answers your question perfectly: https://blog.crossjoin.co.uk/2017/06/11/query-folding-and-writing-your-own-sql-queries-in-power-querypower-biexcel-get-transform/
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/
