help to create my first dataflow from a SQL table.

[speedramps]

Hi

Please can you help me create my first dataflow from a SQL table.

I have done the following:-

• Installed an instance of Microsoft SQL Server Developer (64-bit) on my laptop
• Created database and table with data
• Created a sqlbrowser username and password
• Enabled TCP and firewall port
• Downloaded and installed on my laptop a On-premises data gateway (personal mode)
• Tested the sqlbrowser, user name, TCP and firewall port, by logging into the instance from another machine and running queries on the database tables.
• In Power DeskTop I have created a PBIX with an imported dataset and report and published it to Power BI services
• Tested the gateway refresh on Power BI services ok. Note the gateway uses my Power BI sign-on and then my sqlbrowser username credentials.

Ok, so now I know everything is working ok I want to create my first SQL dataflow. 

• In Power Bi Services I created a workspace
• Clicked on +Create > Dataflow > Add new entities > Blank query
• Copy and paste in my query from Desktop  (I hidden my real TCP address here for security)

    Source = Sql.Database("123.456.7,89,4321", "Testdata", [CreateNavigationProperties=false]),
    dbo_Products = Source{[Schema="dbo",Item="Products"]}[Data]
    in
    dbo_Products

• When I click “select gateway” the drop down menu says “none”
  So I get the error “An on-premises data gateway is required to connect.”

What am I doing wrong?

Why cant it find my  On-premises data gateway (personal mode)?

Please can you provide detailed instructions because this is my first time using sql dataflows.
Many thanks

[v-xicai · Accepted solution]

Hi @speedramps ,

Dataflow will not work with a personal gateway.  If you have a personal gateway, you may like to consider uninstalling it and installing the enterprise version instead.  See more about install and setting for the enterprise gateway : What is an on-premises data gateway?.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nickyvv]

The name of your source (server + database) has to be identical to the name in the gateway, because that is case sensitive in dataflows. So please check that.

[speedramps]

Hi nickyvv

Thank you, but that is not the answer.

I have just read "Dataflows are only supported using Enterprise Gateways."

I am using an On-premises data gateway (personal mode) !
https://docs.microsoft.com/en-us/power-bi/transform-model/service-dataflows-on-premises-gateways
I will try set-up and use Enterprise, unless anyome knowd a workarround on how to create a SQl dataflow without a Enterprise Gateway