Forum Discussion
Solved
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
(
SEL...
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
Memorable Member
Memorable Membermy 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