Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
damit23183
Employee
Employee

Create Dataflow using Datasource under Gateway

Hi,

 

I want to create dataflow using Datasource I have created under Gateway.

 

How to do that process?

 

If i want to use M query then what should be there in Source.

 

Let

Source = ?

In

 

I am aware of Datasource ID and Gateway ID but not sure how to get those id and put that in M code above?

Any guidance or help appreciated.

1 ACCEPTED SOLUTION

Hi @damit23183 

If you have already created a Gateway datasource (Under GATEWAYS, SETTINGS, MANAGE GATEWAY). You can connect with Power BI Desktop, selecting the same database type location and credentials that you entered into the Gateway).

 

Power BI Desktop can't connect to a datasource via a Gateway, you need to select the database.

The normal process is the Gateway is only used for the Power BI Service.  You normally connect to the server via Power BI Destkop internal to the network that has your Server.  When you publish the workbook to the service, the service then uses the gateway to access the data (rather than an internal direct connection like used in Power BI Desktop).

 

Assuming your issue is you can't connect with Power BI Desktop to the server,   I suggest first check you can connect to the server via SSMS or similar (you could also just use Excel).  If that works, then it is most likely incorrect credentials stored in Power BI Desktop.  Sign out (File\SignOut) and then clear your credentials cache (File\Options and Settings\Data Source Settings).  Just delete the ones that could be causing the problem - then try again.

 

There is also a Power BI contruct called a Dataflow, it was that I thought you were using in my first response.

 

Hope this helps

Stuart

View solution in original post

4 REPLIES 4
v-robertq-msft
Community Support
Community Support

Hi, 

Has Burningsuit’s reply helped you to find the solution to this issue?

If so, would you like to mark this reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

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

Burningsuit
Resident Rockstar
Resident Rockstar

Hi @damit23183 

When you create the Dataflow, select "add new tables" in the Dataflow, then select the Database type from the Datasources available. Enter the Server and Database you want to connect to. If that server is gatewayed for you you'll see the Gateway credentials will fill in. If they don't auto fill you can try entering the Gateway and authentication details there.

Clicking "next" should connect you through the Gateway to the Database, select the Table(s) you want to query and click "transform data". This generates the following M code for my example SQL database.

 

let
  Source = Sql.Database("XXXXX.database.XXX""wines"),
  Navigation = Source{[Schema = "dbo", Item = "Winesales"]}[Data]
in
  Navigation
 
Hope this helps
Stuart

Hi,

 

Thank you for your response Really appreciate it!

 

So, this will be the new dataflow and I need to start from scratch. As you said first step would be add tables but to add tables i need to connect somewhere (Datasource) that is what I am looking for.

 

I tried to SQL database, Blank QUery but none of that are working.

Is there any other datasource cateogry I need to use?

 

I am trying to connect DATA SOURCES created in GATEWAYS which you can see under SETTINGS --> MANAGE GATEWAY--> Under Gateway there is Datasource.

 

Thanks

Hi @damit23183 

If you have already created a Gateway datasource (Under GATEWAYS, SETTINGS, MANAGE GATEWAY). You can connect with Power BI Desktop, selecting the same database type location and credentials that you entered into the Gateway).

 

Power BI Desktop can't connect to a datasource via a Gateway, you need to select the database.

The normal process is the Gateway is only used for the Power BI Service.  You normally connect to the server via Power BI Destkop internal to the network that has your Server.  When you publish the workbook to the service, the service then uses the gateway to access the data (rather than an internal direct connection like used in Power BI Desktop).

 

Assuming your issue is you can't connect with Power BI Desktop to the server,   I suggest first check you can connect to the server via SSMS or similar (you could also just use Excel).  If that works, then it is most likely incorrect credentials stored in Power BI Desktop.  Sign out (File\SignOut) and then clear your credentials cache (File\Options and Settings\Data Source Settings).  Just delete the ones that could be causing the problem - then try again.

 

There is also a Power BI contruct called a Dataflow, it was that I thought you were using in my first response.

 

Hope this helps

Stuart

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors