cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Responsive Resident
Responsive Resident

help to create my first dataflow from a SQL table.

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: help to create my first dataflow from a SQL table.

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.

View solution in original post

3 REPLIES 3
Highlighted
Super User I
Super User I

Re: help to create my first dataflow from a SQL table.

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.


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

Proud to be a Super User!


Blog: nickyvv.com | @NickyvV


Highlighted
Responsive Resident
Responsive Resident

Re: help to create my first dataflow from a SQL table.

 

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

 

 

 

 

Highlighted
Community Support
Community Support

Re: help to create my first dataflow from a SQL table.

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.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors