cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
inspirz Frequent Visitor
Frequent Visitor

Advanced Editor from SQL Database

Hi,

 

I created a Direct Query to a SQl database to bring in production rate information. When I click "Get Data" and then select the SQL table and right click and then I choose "Edit" and then select "Advanced Editor" which brings up a pop-up with SQL'ish type language (please see below, edited of course). In that SQl table is a column called "Stream IDs" and I want to create a query to bring in specific "Stream IDs" and not all of them? Could someone please let me know how to do that? In SQL I would have simply wrote "... where Stream_IDs = '1' or Stream_IDs = '2' and et cetera...

 

let
    Source = Sql.Databases("servername"),
    servername2 = Source{[Name="servername2"]}[Data],
    dbo__table_name = servername2{[Schema="dbo",Item="_table_name"]}[Data]
in
    dbo_table_name

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Advanced Editor from SQL Database

this is where you will put your query:

 

q.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

10 REPLIES 10
inspirz Frequent Visitor
Frequent Visitor

Advanced Editor from SQL Database

Hi,

 

I created a DIRECT QUERY to a SQL database to bring in large amounts of refinery production rate data. I want the query only to bring in certain stream id's and not show all the steam id's. So, when I click "Get Data" select the SQL table and rt click it I get an "Edit" option and it brings up the "Query Editor" and when I click "Advanced Editor" I get a window that looks like quasi-SQL language (please see below). In the table is a column called "Stream IDs" and I want to write into the query so it brings back only Stream Ids "1,2,3,4,5,6" abd et cetera. How do I write that?

 

let
    Source = Sql.Databases("server_name"),
    Servername2 = Source{[Name="Servername2"]}[Data],
    dbo_table_name = Servername2{[Schema="dbo",Item="dbo_table_name"]}[Data]
in
    dbo_table_name

Super User
Super User

Re: Advanced Editor from SQL Database

The better approach would be to create a view in you sql db and use that view, although what you mentioned here can still be achieved.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





inspirz Frequent Visitor
Frequent Visitor

Re: Advanced Editor from SQL Database

The DB admin won't create that view because she's worried we'd have a million views for various people.

Super User
Super User

Re: Advanced Editor from SQL Database

Hi @inspirz,

 

can test this out but believe tha you can select the direct query and then the advance options you can write your SQL statement like you would:

 

Select 

    StreamID,

    Description

From

    Table

Where

    StreamID = 1 or StreamID = 2

 

Regards,

Mfelix

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




inspirz Frequent Visitor
Frequent Visitor

Re: Advanced Editor from SQL Database

Thanks a lot. I'm new to all this but I thought in Power BI Advanced Editor the power Query language had to be structured with "let" and "in" and not "select" "from" "where" but I'll try it out.

Super User
Super User

Re: Advanced Editor from SQL Database

this is where you will put your query:

 

q.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

inspirz Frequent Visitor
Frequent Visitor

Re: Advanced Editor from SQL Database

ok that didn't work....

Super User
Super User

Re: Advanced Editor from SQL Database

what didn't work? What error you are getting? Make sure to enter your "sql server name" in server.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





inspirz Frequent Visitor
Frequent Visitor

Re: Advanced Editor from SQL Database

Thanks. That worked. I feel like an idiot now.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 197 members 2,513 guests
Please welcome our newest community members: