How do I set up a data flow in Dataverse on OData or JSON with basic authentication?

Data flows within Dataverse within Power Automate / Power Apps allows two types of authentication for OData and JSON:

  • Anonymous
  • Organization account

Other approaches, such as basic authentication, available through the Power BI Desktop and PowerBI.com refreshes are not supported. It is unknown why not.

However, this topic will explain to you a workaround using Power Query to create a data flow on a OData or JSON resource protected by basic authentication.

Execute the following steps:

  • Make sure the Bridge Online Client IP-address contains all possible IP-addresses from Dataverse or “*” to disable IP-address checks.
  • Go to Data Flows in the menu on https://make.powerapps.com.
  • Choose “New data flow”.
  • Choose “Start from scratch”.
  • Choose the data source “Empty query”.
  • Copy and paste the code below.
  • Adapt URL, user, password, JSON structure and column names where necessary.

The query contains and processes the credentials in an unencrypted format. Depending on your security policy and setup, this may introduce a security risk. Please make sure to match actual security setup against the applicable security policy.

Power Query Code

let
  Source = Web.Contents("https://bridge-online.cloud/acme-database/odata4/ActiveCampaign.V3.Accounts@acn", [Headers=[#"Accept"="application/json",#"Authorization"= Text.Combine({"Basic", " ", Binary.ToText(Text.ToBinary("john.doe@acme.com:secret"), BinaryEncoding.Base64)})]]),
  Rows = Table.FromList(Json.Document(Source)[value], Splitter.SplitByNothing(), {"Columns"}, null, ExtraValues.Error),
  Accounts = Table.ExpandRecordColumn(Rows, "Columns", {"id", "name", "accountUrl", "createdTimestamp", "updatedTimestamp"})
in
  Accounts

Result

The result in Power Apps / Power Automate will be similar to the following image: