Use of Connector in a Dataflow

relax528 commented 2 years ago

I tried transporting my power query code from Power BI Desktop to a dataflow. The connector cannot sign in. I get the following message. I am connecting through the same gateway that the power bi dataset is using to refresh. So I know all that works. I pasted the M code from PBI desktop to a blank query in the dataflow. Selected the same gateway. Click Next, and I get the message I click on configure connection and get this dialog. Click connect, it started to refresh and then goes back to the please specify how to connect. I click the configure connection again, this time click Sign in as a different user... the login window briefly appears, then goes away and I get this message.

I've made sure that https://app.powerbi.com/ has popups enabled.

Is there some sort of technical limitation as to why this wouldn't work in a dataflow?

klinejordan commented 2 years ago

Unfortunately custom connectors are not yet supported in dataflows

mitchonfire commented 2 years ago

Unfortunately custom connectors are not yet supported in dataflows

Hi @klinejordan, although this is true, it is possible to connect to the power bi api, using only a client id and secret of an app registration (no username or password required / Service Principal Auth from Power BI Service)

With this you can do everything that this connector is doing in a dataflow.

I have this working on my tenant. Have I managed to do something unique in the above connectivity? Seems like nobody knows how to get it right?

from what I have read on the internet over the last year or so, I have had a very hard time getting this right but finally have (probably/I think the Service Principal Auth preview has enabled this to work).

I also have a full semantic tabular model which brings all the data together and relates it (imports from dataflow). (although this is highly enriched with our corporate data sources so not in a usable for for anyone else right now)

klinejordan commented 2 years ago

@mitchonfire I've seen this work before by acquiring the token through one query and then using it as the authorization header in an API call like you would in a real object oriented program, but no solution I've found can encrypt the Client ID and Client Secret properly using M. How did you do it?

migueesc123 commented 2 years ago

The AAD authentication is embedded in Power BI Desktop by default using some (M)agic.

In other words, in theory you could connect to any REST API that uses the AAD flow and query that api without any issues or going through the lengths that something like other connectors with oauth 2.0 have to go through (like the YouTube analytics connector that I have).

The main issue that you'll encounter using just the native power query functionality is that:

You won't be able to get the contents in the response header
Trying to make the pagination work flawlessly is going to be near impossible
You won't have the cool navigation table that we currently have or the full "get data" flow that you can only accomplish with a connector

There might be some other concerns / issues, but they are probably around usability / accessibility concerns.

mitchonfire commented 2 years ago

@mitchonfire I've seen this work before by acquiring the token through one query and then using it as the authorization header in an API call like you would in a real object oriented program, but no solution I've found can encrypt the Client ID and Client Secret properly using M. How did you do it?

Hey @klinejordan, yes I am getting the token using _grant_type = "clientcredentials" And yes, that is the one caveat, you cannot encrypt the secret, you have to fetch it from a secured source if you need to secure it.

mitchonfire commented 2 years ago

The AAD authentication is embedded in Power BI Desktop by default using some (M)agic. ...

Thanks @migueesc123,

I (think) I am getting the response in the header, doing a single PUSH call with token, which returns the response binary content.
I had got your activity pagination working fine (but I have since replaced the activity calls to MS Cloud App Security API for activity as I find it far more responsive and an easier interface)
yes, no navigation GUI in this approach, however I have always steered clear of those anyway, prefer to code everything

let me know if anyone wants a code snippet.

I have also got the new scanner api working in a call, wait for result, and then process scan result

migueesc123 commented 2 years ago

@mitchonfire is not possible to get the contents of the response header in the regular Power Query. This is actually by design as a security concern.

The only way to get the contents of the response header is by using Value.MetaData against the output of your Web.Contents - this is the primary reason why some things are only meant to happen at the custom connector level and can't even be done with regular Power Query.

You could port the whole code of the connector into a dataflow project and kinda make it work by tweaking the code. However, while it is theoretically possible to make that happen, only advanced users would know how to make that happen and the goal of the custom connector (and this repo) is to provide a clean and easy way to query the rest api without. In other words, a power bi user friendly way to query the rest api.

You're more than welcome to contribute to the repo!

migueesc123 / PowerBIRESTAPI

Use of Connector in a Dataflow #89