Importing data from the ACLED API into PowerBI

This guide explains how to integrate the updated ACLED API into PowerBI using the OAuth authentication method.

Step 1. Clear any stored credentials

1. File → Options and settings → Data source settings.
2. Look for any entries for https://acleddata.com. Select each one and choose Clear Permissions.
3. You will reconnect as Anonymous in a moment.

Step 2. Create parameters for secrets

This keeps sensitive values out of the code pane.

1. In Power BI Desktop, click Transform data.
2. Home → Manage Parameters → New.
3. Create three Text parameters
   • pUsername with your ACLED username
   • pPassword with your ACLED password
   • pClientId with value acled
4. Leave Required ticked. Click OK 

Step 4. Create the data query that uses the Bearer token

1. Home → New Source → Blank Query → Advanced Editor.
2. Paste the script below and rename to something like ACLED_Read.

let
  BaseUrl = "https://acleddata.com/",
  // Token request
  FormBody = Uri.BuildQueryString([
      username   = pUsername,
      password   = pPassword,
      grant_type = "password",
      client_id  = pClientId
  ]),
  TokResp = Web.Contents(
              BaseUrl,
              [
                RelativePath = "oauth/token",
                Headers = [ #"Content-Type" = "application/x-www-form-urlencoded" ],
                Content = Text.ToBinary(FormBody),
                Timeout = #duration(0,0,90,0),
                ManualStatusHandling = {400,401,403,404,409,415,429,500,502,503,504}
              ]),
  TokStatus = Value.Metadata(TokResp)[Response.Status]?,
  TokText   = try Text.FromBinary(TokResp, TextEncoding.Utf8) otherwise "",
  TokJsonTry = try Json.Document(TokResp),
  AccessTok =
      if TokStatus <> 200 or TokJsonTry[HasError] then
          error "Token call failed. Status=" & Text.From(TokStatus) &
                " Body=" & Text.Start(TokText, 400)
      else
          TokJsonTry[Value][access_token],
  // Data request
  DataResp = Web.Contents(
               BaseUrl,
               [
                 RelativePath = "api/acled/read",
                 Headers = [ Authorization = "Bearer " & AccessTok, Accept = "application/json" ],
                 Query = [ limit = "50" ],
                 Timeout = #duration(0,0,90,0),
                 ManualStatusHandling = {400,401,403,404,409,415,429,500,502,503,504}
               ]),
  DataStatus = Value.Metadata(DataResp)[Response.Status]?,
  DataText   = try Text.FromBinary(DataResp, TextEncoding.Utf8) otherwise "",
  DataJsonTry = try Json.Document(DataResp),
  Json =
      if DataStatus <> 200 or DataJsonTry[HasError] then
          error "Data call failed. Status=" & Text.From(DataStatus) &
                " Body=" & Text.Start(DataText, 400)
      else
          DataJsonTry[Value],
  // Find rows and table them
  Rows =
      if Value.Is(Json, type list) then Json
      else if Record.HasFields(Json, "data") then Json[data]
      else if Record.HasFields(Json, "results") then Json[results]
      else if Record.HasFields(Json, "items") then Json[items]
      else error "Could not find a rows list. Top level keys: " &
                 (if Value.Is(Json, type record)
                  then Text.Combine(Record.FieldNames(Json), ", ")
                  else Value.ToText(Value.Type(Json))),
  Out =
      if Value.Is(Rows, type list) then
          if List.Count(Rows)=0 then #table({}, {}) else Table.FromRecords(Rows)
      else if Value.Is(Rows, type record) then
          Table.FromRecords({Rows})
      else
          error "Rows is neither a list nor a record."
in
  Out 

Step 5. Confirm the data source is Anonymous

• Data source settings for https://acleddata.com should be Anonymous and the same privacy level. Since we kept a single root with RelativePath, privacy will not try to block, but consistent settings help at load time.
• In File → Options and settings → Options → Current File → Privacy, leaving Ignore Privacy Levels on for this PBIX is fine in Desktop while we are building. For a published refresh, the single root plus RelativePath is the safe pattern.