Report using SQL - Unable to edit under applied steps settings?

[rodneyc8063]

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

[gokberkuzuntas]

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,
gokberkuzuntas

LinkedIn: 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.

[rodneyc8063]

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"&"]"

[gokberkuzuntas]

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,
gokberkuzuntas

LinkedIn: 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.

[rodneyc8063]

Ahh ok I think I get what your saying now! (New to PBI sorry!)

I clicked on the "Prior Fiscal Year - Same Period" table -> Advanced Editor and its a bunch of code but I do see the following line

"......between #(lf)dateadd(year, -1, '"&EndDate&"')...."

Im guessing this is a parameter in the query and thats why I cant see the "Edit Settings" option under Applied Steps?

Sorry and going to sound really silly here to ask this but I tried to re-create your screen shot (blank query with let source ="data") but where do you go to see the "original M code"?

Original M code:

Source = Sql. Database(SQLServerName, DBName, [Query="select * from ["&#"Query3"&"]"

And where do you find the "fetched hidden code"?

The fetched hidden code will be like:

Source = Sql. Database(SQLServerName, DBName, [Query="select * from [data]"

When you refer to "blank query text" do you mean it could be literally any sort of text? Like

your example "data" or "sample1" etc?

I am just a bit lost on how you were able to find that M code and fetched hidden code etc now but I really appreciate your patience! 😀

[uzuntasgokberk]

Hello @rodneyc8063,

I had a issue with my user account. Therefore, sorry for late replying.

Yes exactly ""......between #(lf)dateadd(year, -1, '"&EndDate&"')...."" that's why you can't see grey icon.

There is no fetched hidden code. I just try to explain that understanding behind the logical.

Blank Query Text is just example. İt can be parameter that only include one value.

I have some privacy databases etc. Therefore, I gave this example.

You just done based on the EndDate such as 2024 etc.

Make sense?

Best Regards,
gokberkuzuntas

LinkedIn: 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.