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:
