Edit SQL in Power Query

Question

When I import data from Sql Server in Power Query, I can paste a query to be executed against the database. But later when I want to edit this query in Workbook Queries > Edit > Advanced Editor, I get something like this:

let
    Source = Sql.Database("server", "database", [Query="select#(lf)#(tab)*#(lf)from dbo.SomeView va#(lf)join dbo.SomeTable rm#(lf)#(tab)on rm.CatId=va.CatId#(lf)where 1=1#(lf)#(tab)and Month between 1501 and 1510#(lf)#(tab)and rm.Id in (1,2,3)"])
in
    Source

Please note that I'm using *, but with explicit column names this would look even worse.

I'd like to be able to see the query in a readable form, then copy it, execute in Management Studio, change something and paste back to Power Query. I know I could be using views as a source, or not using newlines and indentation, but that's not my point.

Any ideas on how to edit SQL in "normal" form? Or maybe I'm missing some hidden option.

EDIT: In case I'm not the only person in the world having problems finding this option, it's in:

Power Query > Launch Editor > View > Query Settings > Applied Steps > Source > Gear icon

Thanks Alejandro!

Thanks for posting that path. However, what you get after clicking the gear icon hardly resembles SQL style. — Przemyslaw Remin, Jan 16 '18 at 09:21

score 35 · Accepted Answer · answered Nov 12 '15 at 19:15

35

If you click on the gear icon next to Source in the Query Settings pane you'll get a dialog with the SQL query in a readable form.

answered Nov 12 '15 at 19:15

Alejandro Lopez-Lago - MSFT

4,014
20
25

1

clicking on the gear icon next to the Source in Applied Steps will "undo" the Powerquery source command/"query" into the SQL that you pasted in initially. Much easier than editing the raw Powerquery source string as-is in a text editor, or even in situ in the Advanced Editor! Let's save the "SQL Style" arguments for another time and dimension. – user1390375 Sep 28 '18 at 20:47
5

Why did they hide this? Chances are good that PowerQuery users will know SQL. – Spivonious Apr 16 '19 at 19:04
1

When you use parameters in the SQL query, this solution no longer works... The gear icon don't show the query. – Gauss Apr 18 '19 at 14:54

score 11 · Answer 2 · edited Apr 08 '21 at 22:46

11

Here's an illustration that complements the accepted answer.

edited Apr 08 '21 at 22:46

Marco Bonelli

63,369
21
118
128

answered Jun 19 '20 at 20:53

rtev

1,102
12
24

Edit SQL in Power Query

2 Answers2

Linked