Forum Discussion
Report using SQL - Unable to edit under applied steps settings?
Howdy folks,
My team is new to PBI and I am trying to help out a co worker with trouble shooting a PBI report hes working on currently and I had a few questions I was hoping for some help on.
So firstly we have some pre-written SQL statements and for a quick proof of concept was thinking to just use these in PBI so our tables will be set up. I know in the database we could perhaps create views and then import these into PBI. It looks like though my co worker is wanting to try having the SQL in PBI and not have an extra object in our database.
So my first question is - Where can we input the SQL?
I saw one way is to go to Get Data -> SQL Server and then under Advanced options theres a section for a SQL statement
Would this be the "only" place where we can input a SQL statement?
Next question is lets say I want to go back and modify a SQL statement. Checking the PBIX file I have I can see that under "Applied Steps" on the right hand side there is a "Source" step. Theres a gear icon I can click on and I can see the following pop up window
So looks like I can go here to edit or view the current query.
But now if I check the table just below this one I noticed that at the source step there is NO gear icon?
I tried to click on the "Advanced Editor" and I see a let source = style statement with what appears to be the SQL
I was wondering why is this so? Why did the gear icon "go missing"?
Just trying to make sure I didnt miss something fundamental and would appreciate some guidance.
Thanks in advance
5 Replies
Hello rodneyc8063,
First of all, in the SQL side you can write a sql view and connect directly from Power BI to SQL view. But if it is not possible, yes this is the way from advance setting that you can write sql statement.
Secondly, grey icon is disapered because you probably use one of your parameter or blank query that includes text value etc.. in your SQL side.
For example:
"
let
in
Source = Sql. Database(SQLServerName, DBName, [Query="select * from ["&#"BlankQueryText"&"]"
Source
"
That caused not to see grey icon
Best Regards,
gokberkuzuntasLinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/
Medium: https://medium.com/@uzuntasgokberk
İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.
Appreciate the quick reply
Follow up question please for the greyed out "Settings" option under Applied steps.
...or blank query that includes text value etc.. in your SQL side.
For example:
"
let
in
Source = Sql. Database(SQLServerName, DBName, [Query="select * from ["&#"BlankQueryText"&"]"
Source
"
Sorry what do you mean by "blank query that includes text value"?
I admit I am not familiar with M language so cant quite figure out what this section means/does? -> "select * from ["&#"BlankQueryText"&"]"
For example you can create blank query in Power Query(Transform Data) side that you can give value(Picture 1.1). So the value from Query3 is assume that your database name and when you use in your M language code Query3 value is fetched to your code.
Original M code:
Source = Sql. Database(SQLServerName, DBName, [Query="select * from ["&#"Query3"&"]"
The fetched hidden code will be like:
Source = Sql. Database(SQLServerName, DBName, [Query="select * from [data]"
Picture 1.1
The important thing is do you have such as ["&#"BlankQueryText"&"]" in your Prior Fiscal Year table(SQL query). Because I couldn't see your whole sql code. İf you have like such M code ["&#"BlankQueryText"&"] that's why you can't see grey icon.
Best Regards,
gokberkuzuntasLinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/
Medium: https://medium.com/@uzuntasgokberk
İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.
