Dataflows - Authentication using API Key or OAuth 2.0

[hsu]

Dear community users,

I try to use Dataflows to get the data from REST API (OData or OpenAPI) into the CDS.

The problem I face is that for 1 particular API, it just supports API Keys or OAuth 2.0

From the PowerApps, I can connect to that API from the Custom Connector, which supports API Keys and OAuth 2.0.

However from the Dataflows, I cannot authenticate, since there is no authentication method using API Keys and OAuth 2.0. 

Anyone can help on this:

• Any information whether Dataflows will support API Keys and OAuth 2.0?
• Do you have any other workaround to get the data into the CDS from a REST API (OData or OpenAPI) data source?

Thanks.

Below are screenshots from the Dataflows Settings UI, only showing Authentications Mode

• Anonymous
• Basic Authentication
• Windows
• Organizational Account

With Gateway

API Endpoint with Gateway

Without Gateway

API Endpoint without Gateway

[jlindstrom]

I don't know about timeframe, but if you want to use API key, you can do that now using other integration methods, such as SSIS. Also Power Automate and Logic apps support using API key via service principal

https://powerofpowerplatform.com/using-service-principal-in-power-automate-flow/

[EricRegnier · Accepted solution]

Hi @hsu,

Unfortunately, Dataflows OData connector does not yet support API Key and Client ID, Secret authentication. Suggest to submit the idea at https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas.

Cheers

[P3Sven]

Hi!

I haven't found any ideas about this. So I've created one here:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Scheduled-Dataflow-with-Custom-Connector-OR-Sta...

Or do you already have a nice other workaround? Maybe how to use custom connectors in Dataflows (not Power Automate) ?

[testasdfasfsafa]

3 years later, cannot access web API requiring bearer token, useless tool 😞

[EricRegnier]

@testasdfasfsafa, sorry to hear your getting blocker with Dataflows, it does support over +50 connectors which is pretty cool for a low code/no code ETL tool. You should be able to set a bearer token by modifying the headers directly in the Advanced Editor:

Also, feel free to submit the ideas for any limitations or feature requests: https://ideas.powerapps.com/d365community/mycontent/

Hope this helps!

[testasdfasfsafa]

Hi Eric,

Thanks for your reply.  Just to be clear, are you saying I can simply paste a bearer token, or I can fetch/get a dynamic token then use it call the data API?  If it's possible, then it would really useful since data sources often live behind token protected APIs.

[EricRegnier]

@testasdfasfsafa, exactly but the caveat is you'll have to manually maintain it if the token expires and/or changes per environment (test/prod). Cheers 

[testasdfasfsafa]

hmmm... it would not be useful since the token expires after 1 hour.  What would be great is for power automate to fetch the token, then pass it on to dataflow and run the dataflow...  I can't find a way to do that either 😞

[TomTomTom]

I am having the same issue. I have an API I call that requires a subscription-key. I can get it working in a Power BI dataset - but then scheduled refresh doesn't work in the Power BI service. In Dataflows, I can't get Power Query to accept the key name and value at all - I've tried kludging it into Headers or Queries.

Any ideas?

It's a very useful dataset for us to query (reviews on a big website) and they are publicly available on the web.