How do I create a Dataset connected to a Gateway Data Source?

[TheNItzel]

I feel like I'm missing something extremely simple and a bit angry that after 2 hours I haven't found it.  We have a data gateway setup.  We have known working data sources configured and online.  How do I create a report and/or dataset that is connected to that working data source (via the Power Bi Gateway).  I don't have Desktop on anything that can talk directly to the actual SQL that the data source is configured to.  When I create a Dataset, there is no option to connect to SQL, or the Gateway, or a Data Source.  That feels like a gap.  When I try to use Desktop, there's no Gateway/Data Source option that I see which also feels like a gap.  When I configure the connection settings there (matching identically what the Power Bi Gateway Data Source is configured to use), it says it cannot connect to it (which I've learned is because it's trying to reach it directly instead of using creds to reach the Power Bi Gateway Data Source I've configured and have access to, so yet again another gap I feel needs to be filled as we sit here at the end of 2023).  Please please tell me I'm missing something extremely obvious and this isn't a poor design on Microsoft's part. 

[aj1973 · Accepted solution]

Hi @TheNItzel 

It sounds like you are mixing all up. You don't need Gateway to connect Power BI desktop to SQL, you just to choose the right connector. Where is the gateway installed? On premise? Then you will need it to create a dataflow in the service.

[TheNItzel · Accepted solution]

So for anyone who stumbles upon this in the future, Amine/aj1973 is correct.  The answer is to create a Dataflow instead of a Dataset.  For whatever reason there seems to be two MS docs around Power Bi Data Gateways and one tells you to use Dataset which still requires a network connection to your Power Bi Desktop to INITIALLY establish and then the other tells you how to create a Dataflow which is done through the Power Bi Service website.  ALSO you must not create this from "My Workspace" but instead create/have an existing standalone Workspace, then choose New and choose Dataflow from there.  At that point you can select your SQL/Whatever data source type matches the one you created as a Data Source in the Power Bi Data Gateway configuration pages.  Copy the server information exactly as you have it from your configured Data Source into the the Dataflow connection page and it will transform magically into showing you the name of the Data Source name you configured on the Gateway page.  You can then go to Power Bi Desktop from ANYWHERE that has an internet connection, choose "Get Data" then choose "Dataflows", sign in with the account that has access to that Workspace/Dataflow you created, then choose the dataflow you made and see the tables.  Magic.    

[TheNItzel]

Hi @aj1973 

The Gateway is installed on-premise where the on-premise SQL server lives.  My computer is on a Starbucks wifi connection in the Bahamas with no VPN. 

[aj1973 · Accepted solution]

Hi @TheNItzel 

It sounds like you are mixing all up. You don't need Gateway to connect Power BI desktop to SQL, you just to choose the right connector. Where is the gateway installed? On premise? Then you will need it to create a dataflow in the service.