Power Query - Can a range be queried using SQL?

Question

I have a range named tDetails. How can I query this using SQL?

I click in my range, and on the Power Query tab, I click "From Table/Range". This opens the Power Query editor.

In the advanced editor, I see something like:

let
    Source = Excel.CurrentWorkbook(){[Name="tDetails"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", type text},
    {"Company", type text}, 
    {"State", type text},
    {"Billed", type Int65.Type}})
in
    #"Changed Type"

How do I go about querying this data? I'm needing to do things like:

SELECT 'UserID'   = "User ID"
,      'Discount' = "Billed" * .51
,      'Note'     = CASE WHEN State = 'NY' THEN 'Standard'
                         WHEN State = 'OH' THEN 'Second'
                         WHEN State = 'CA' THEN 'Third'
                         ELSE 'Fourth' END
FROM    Source
WHERE   "State"   = "NY"

Also, can temp tables be used here?

SELECT 'UserID'   = "User ID"
,      'Discount' = "Billed" * .51
INTO    #UserDiscount
FROM    Source
WHERE   "State"   = "NY"

SELECT * FROM #UserDiscount ORDER BY Discount DESC

These are super basic examples. What I really need to do is far more complex, but this would give me a place to start.

Thanks!

Answer 1

I think the equivalent Power Query would be

    let
    Source = Excel.CurrentWorkbook(){[Name="tDetails"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", type text},
    {"Company", type text}, 
    {"State", type text},
    {"Billed", type Int65.Type}})
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([State] = "NY")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Discount", each [Billed]*0.51),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "UserID", each [UserID]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"UserID", "Discount", "Billed"})
in
    #"Removed Other Columns"

Also, the equivalent of SQL #temp table in Power Query would be use of table variable. In power query each step is table variable, e.g. Source, #"Changed Type", #"Filtered Rows", #"Added Custom".... which contains the table that was evaluated in that step. You can keep on utilizing the tables contained in each table variable in succeeding steps.

e.g.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcrPS1WK1YlWMgKyS8rzwWxjEDujKBUoEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}),
    Custom0 = Table.SelectRows(#"Changed Type", each ([Column2] = "one")),
    Custom1 = Table.SelectRows(#"Changed Type", each ([Column2] = "two")),
    Custom2 = Table.SelectRows(#"Changed Type", each ([Column2] = "three")),
    Custom3 = Custom0&Custom1&Custom2
in
    Custom3

Answer 2

Yes, but you have to use a reserved function SqlExpression.ToExpression, and it doesn't look like your SQL is "valid" according to Power Query which likely needs in T-SQL format, so you'll have to experiment with how quotes are handled and columns renamed.

See this page for details: https://bengribaudo.com/blog/2021/07/13/5868/m-mysteries-sqlexpression-toexpression

Queries:

// tDetails
// Input from Excel range. This example uses embedded data so you can replicate my result.
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRcs7PK8kvzgey/CKBhKGRsYlSrE60kldqWhqKtL8HSNoADJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Company = _t, State = _t, Billed = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", type text}, {"Company", type text}, {"State", type text}, {"Billed", Int64.Type}})
in
    #"Changed Type"

// Result
// Executes translated M code
let
    ReferenceTables = [Source = tDetails],
    Source = Expression.Evaluate(SQL, #shared)(ReferenceTables)
in
    Source

// SQL
// Translates SQL to M
let
  ReferenceTables = [Source = tDetails],
  SqlQuery = "SELECT [User ID] as UserID, Billed * 0.51 as Discount, 'Standard' as [Note] FROM Source WHERE State = 'NY'",
  TranslatedToM = SqlExpression.ToExpression(SqlQuery, ReferenceTables)
in
  TranslatedToM

tDetails:

My SQL that works: SELECT [User ID] as UserID, Billed * 0.51 as Discount, 'Standard' as [Note] FROM Source WHERE State = 'NY'

Result: