Converting from SQL to M

[Artefx]

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

[sulli01]

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-quer...

[ImkeF]

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...

[Eric_Zhang]

@Artefx

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.

[samdthompson · Accepted 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.

[Artefx]

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