M Mysteries: SQL -> M (SqlExpression.ToExpression)

8 thoughts on “M Mysteries: SQL -> M (SqlExpression.ToExpression)”

1. Triparna Ray July 13, 2021 at 11:05 pm

   Hi Ben,

   Thanks for the great write-up. I have a use case of joining two tables in power query based on multiple column including one column with between clause of another column. Does that mean I can achieve that using complex SQL queries inside M editor in Power Query.

   Let me know if you think we should achieve it in any other better way.

   Best,
   Triparna

   Reply ↓
   1. Ben Gribaudo Post authorJuly 20, 2021 at 7:32 am

      In Power Query, you could try a Table.Join or Table.NestedJoin that joins all the appropriate columns except for the one where you need the between-like behavior, then write the between logic into a Table.SelectRows filter. My guess is that the between-written-as-a-SelectRows filter, if it folds, will be written into the generated native query as a WHERE clause—but can still be used to achieve the same effect as a BETWEEN predicate.

      Alternately, you could use Value.NativeQuery to explicitly specify a raw SQL statement that is written exactly the way you’d like it to be.

      Reply ↓
2. smpa01 July 21, 2021 at 3:21 pm

   Ben, I followed this but got stuck at the last step. Do you mind taking a look please?

   let
     rt1 = 
       let
         Source = Table.FromRows(
           Json.Document(
             Binary.Decompress(
               Binary.FromText("i45WMlTSUUpUitWJVjICspKUYmMB", BinaryEncoding.Base64), 
               Compression.Deflate
             )
           ), 
           let
             _t = ((type nullable text) meta [Serialized.Text = true])
           in
             type table [id = _t, index = _t]
         ), 
         #"Changed Type" = Table.TransformColumnTypes(
           Source, 
           {{"index", type text}, {"id", Int64.Type}}
         ), 
         #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"index", "sn"}})
       in
         #"Renamed Columns", 
     lft1 = 
       let
         Source = Table.FromRows(
           Json.Document(
             Binary.Decompress(
               Binary.FromText("i45WMlTSUUpUitWJVjICspKUYmMB", BinaryEncoding.Base64), 
               Compression.Deflate
             )
           ), 
           let
             _t = ((type nullable text) meta [Serialized.Text = true])
           in
             type table [id = _t, index = _t]
         ), 
         #"Changed Type" = Table.TransformColumnTypes(
           Source, 
           {{"index", type text}, {"id", Int64.Type}}
         ), 
         #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"index", "sn"}})
       in
         #"Renamed Columns", 
     Ref = [Department = rt1, Company = lft1], 
     SqlQuery = "select r.id, l.sn from Department r left outer join Company l on r.id=l.id", 
     M = SqlExpression.ToExpression(SqlQuery, Ref), 
     Custom1 = Expression.Evaluate(M, #shared)(Ref)
   in
     Custom1

   Reply ↓
   1. Ben Gribaudo Post authorJuly 22, 2021 at 10:43 am

      The M code output by SqlExpression.ToExpression (below) specifies that the join should be done using Value.NullableEquals as the equality comparer.

      (environment) => Table.RenameColumns(let
          t904 = let
              t901 = Table.PrefixColumns(environment[Department], "r"),
              t902 = Table.PrefixColumns(environment[Company], "l"),
              t903 = Table.Join(t901, {"r.id"}, t902, {"l.id"}, JoinKind.LeftOuter, null, {Value.NullableEquals})
          in
              t903,
          t907 = Table.SelectColumns(t904, {"r.id", "l.sn"})
      in
          t907, {{"r.id", "id"}, {"l.sn", "sn"}})

      Apparently, M doesn’t support this when executing the join locally (i.e. it can currently only be used when folding occurs).

      If you edit the generated M so that it doesn’t set the comparer (that is, remove “, {Value.NullableEquals}“), it runs fine locally.

      Reply ↓
      1. smpa01 July 27, 2021 at 2:04 pm

         Many Thanks Ben !!! Do you know which SQL can we use in PQWRY? Is it SQLite..It will certainly make my life easy

         Reply ↓
         1. Ben Gribaudo Post authorJuly 28, 2021 at 12:29 pm

            Likely, it’s a subset of T-SQL.

            Reply ↓
3. Samik Pal August 13, 2021 at 3:35 pm

   Ben, I have been experimenting with this since I discovered this. But can you please confirm it works in dataflow. My test says it does not work. If possible, can you please test it out and suggest if there is a workaround.

   I tried the following
   /*Query 1*/

   let
       Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTTVU0lEyNDBQitVB8I1A/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, value = _t]),
       #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"value", Int64.Type}}),
       ref=[tbl=#"Changed Type"],
       sql= "select [name] as [name], sum(value) as value from tbl group by [name]",
       tran = SqlExpression.ToExpression(sql,ref),
       back= Expression.Evaluate(tran, #shared)(ref)
   in
       back
   

   which is same as
   /*Query 2*/

   let
       Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTTVU0lEyNDBQitVB8I1A/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, value = _t]),
       #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"value", Int64.Type}}),
       #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"sum", each List.Sum([value]), type nullable number}})
   in
       #"Grouped Rows"
   

   Query 2 works in dataflow but Query 1 does not. IT generates the following error
   Can’t save dataflow
   One or more tables references a dynamic data source

   Many thanks in advance.

   Reply ↓
   1. Ben Gribaudo Post authorAugust 30, 2021 at 11:32 am

      I don’t know if there is a work around. SqlExpression.ToExpression isn’t meant for general production use. 🙁

      Reply ↓