Calling an SAP BTP CAP service via OData with API key without saving the key as plain text

Hi Jonas_Trompeter,

If you need to call an SAP BTP CAP OData service using an API key but want to avoid storing the key as plain text in your M code, there are several secure methods you can use. One straightforward approach is to store the API key as a Power Query parameter rather than hard-coding it in the query. This way, the key is entered through the parameter interface, and the M code only references the parameter, so the key does not appear as a literal string. When you publish the report to Power BI Service, you can securely re-enter the key in the dataset settings.

For greater security at the enterprise level, you can keep the API key in a secret vault like Azure Key Vault or SAP BTP Destination/Connectivity Service. Power BI can then use a custom connector or a proxy/API Management layer to retrieve the key at runtime and add it to the request header, ensuring the key is never stored in the PBIX file or M script.

Ideally, consider moving away from static API-key authentication and publishing the CAP service with OAuth2/XSUAA. OAuth2 allows Power BI to authenticate with token-based access, removing the need to store a long-lived secret and offering improved security, easier key rotation, and better governance.

Thank you.