Advanced Editor from SQL Database

[inspirz]

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

[MFelix]

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

[inspirz]

ok that didn't work....

[parry2k]

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

[inspirz]

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.

[parry2k · Accepted solution]

this is where you will put your query:

[AFoulk]

@parry2k

I was wondering if might know these answers. At the bottom of SQL Statement box, do you know what the exact meaning/purpose is for each of those check marks?

"Include relationship columns"

"Navigate using full hierarchy"

"Enable SQl Server Failover support"

[inspirz]

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

[parry2k]

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.

[inspirz]

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

[inspirz]

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