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
– Przemyslaw Remin, Commented Jan 16, 2018 at 9:21

Alejandro Lopez-Lago - MSFT · Accepted Answer · 2015-11-12 19:15:04Z

36

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, 2015 at 19:15

Alejandro Lopez-Lago - MSFT

4,18422 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1390375 Over a year ago

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.

Spivonious Over a year ago

Why did they hide this? Chances are good that PowerQuery users will know SQL.

Gauss Over a year ago

When you use parameters in the SQL query, this solution no longer works... The gear icon don't show the query.

Marco Bonelli · Accepted Answer · 2021-04-08 22:46:22Z

13

Here's an illustration that complements the accepted answer.

edited Apr 8, 2021 at 22:46

Marco Bonelli

71k21 gold badges129 silver badges153 bronze badges

answered Jun 19, 2020 at 20:53

rtev

1,12212 silver badges24 bronze badges

Collectives™ on Stack Overflow

Edit SQL in Power Query

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related