How to Import Data into MS Excel Power BI

In this article, we will walk you through the steps of how to Import Data into MS Excel Power BI with MS Excel Power Query using Secure Code Warrior API.

Authentication
Retrieving Data
Pagination (Do-While Loop)
Creating Reports and Visualtisations
Working Examples

Step 1: Authentication

The first step to using the Secure Code Warrior API is authentication. To obtain an API key please follow the steps in this article.

Step 2: Retrieving Data

The next step is to retrieve the data that you want to analyse. The Secure Code Warrior API allows
you to retrieve data in JSON format. This data can then be parsed and used to create reports
and visualisations in Power BI.

Step 3: Pagination (Do-While Loop)

The API limits the number of records that can be retrieved in a single request. This means that you may
need to make multiple requests to retrieve all the data you need. To overcome this challenge,
you can use a do-while loop.

Note: A do-while loop is a programming construct that allows you to repeat a set of actions until a
certain condition is met. In this case, the condition is the absence of data. You can use a
do-while loop to retrieve data in batches until all the data has been retrieved.

How to wrap a 'do-while' loop around the query:

Step 3.1:

Create the get Data query as you would for any API data pull.

Step 3.2:

Build the query content out with the data (columns) and data types you want. At this point you are only getting the 1st page of data (e.g. the first 500 or 1000 records) but, crucially, it is in the form you want.

Step 3.3:

Convert your Power Query(s) to a Function by Editing the M code to add this as 1st line:
(page as number) as table => and change page=1 in your url to page= and add &Number.ToText(page) at the end of your URL so it knows where to append the incremental page number.

Name the Query (now a Function - fx) as "GetData" - the Function name is referenced in the next step so they must match!

Your Query (which is now a Function) will look something like this:

(page as number) as table =>
let
Source = Json.Document(
Web.Contents(
"https://portal-api.securecodewarrior.com/api/v2/training/developerleaderboard?report_period=30&page="
& Number.ToText(page), 
[Headers = [#"X-API-Key" = "_put_a_real_api_key_here_"]]
)
), 
leaderboard = Source[leaderboard], 
#"Converted to Table" = Table.FromList(
leaderboard, 
Splitter.SplitByNothing(), 
null, 
null, 
ExtraValues.Error
), 
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to Table", 
"Column1", 
{"rank", "developer", "overall_stats_over_report_period", "challenges", "challenges_summary"}, 
{"rank", "developer", "overall_stats_over_report_period", "challenges", "challenges_summary"}
), 
#"Expanded developer" = Table.ExpandRecordColumn(
#"Expanded Column1", 
"developer", 
{
"email", 
"status", 
"company", 
"team", 
"tags", 
"roles", 
"name", 
"member_since", 
"last_logged_in"
}, 
{
"email", 
"status", 
"company", 
"team", 
"tags", 
"roles", 
"name", 
"member_since", 
"last_logged_in"
}
)
in
#"Expanded developer"

Step 3.4:

Create a new blank Query that will use this new Function (fx) you created in Step 3
the new query should read:

  = List.Generate( ()=>
[Result= try GetData(1) otherwise null, page = 1],
each [Result] <> null,
each [Result = try GetData([page]+1) otherwise null, page = [page]+1],
each [Result])

Step 3.5:

Execute the new Query, and load the data to a table/pivot/BI -- it'll pull the data you want, as laid
out in Step 3.2, and execute a 'do-while' loop with the Function you created in Step 3.3.

Step 3.4 is just the "do-while" loop using the handy List.Generate function. This simple piece of M code just evaluates for the next page of data and then stops when it doesn't get any more.

Step 4: Creating Reports and visualisations

Once the data has been retrieved, you can use Power BI to create reports and visualisations.
Power BI provides a variety of tools for creating charts, graphs, and tables. You can use these
tools to create reports that provide insights into their data.

Working Example

Below are working examples of Queries & Functions used in MS Excel with a POST-based SCW API
Endpoint ( https://portal-api.securecodewarrior.com/api/v2/assessments/attempts/search) to allow
the recursive pull of all data pages, thereby dealing with the API pagination limit.

1 and 2 constitute a Do-While loop to pull back all pages until there are no more, whereas 1 and 3
constitute a Fixed loop to pull back a set number of pages.

1. The FUNCTION named "GetData" (converted from a QUERY) in Advanced Editor view

  (pageNum as number) as table =>
let
pageString = Number.ToText(pageNum), 
formdata = "{""page"":" & pageString & "}", 
Source = Json.Document(
Web.Contents(
"https://portal-api.securecodewarrior.com/api/v2/assessments/atte
mpts/search", 
[
Headers = [
#"x-api-key" = "API_KEY****0ff****c6****ea6****b731****API_KEY", 
#"Content-Type" = "application/json"
], 
Content = Text.ToBinary(formdata)
]
)
), 
attempts = Source[attempts], 
#"Converted to Table" = Table.FromList(
attempts, 
Splitter.SplitByNothing(), 
null, 
null, 
ExtraValues.Error
), 
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to Table", 
"Column1", 
{
"developer", 
"status", 
"language", 
"completed", 
"started", 
"deadline", 
"time_spent_ms", 
"completed_in", 
"progress", 
"score", 
"accuracy", 
"passing_grade", 
"pass_status", 
"_id", 
"_assessment", 
"name", 
"challenges"
}, 
{
"Column1.developer", 
"Column1.status", 
"Column1.language", 
"Column1.completed", 
"Column1.started", 
"Column1.deadline", 
"Column1.time_spent_ms", 
"Column1.completed_in", 
"Column1.progress", 
"Column1.score", 
"Column1.accuracy", 
"Column1.passing_grade", 
"Column1.pass_status", 
"Column1._id", 
"Column1._assessment", 
"Column1.name", 
"Column1.challenges"
}
), 
#"Expanded Column1.developer" = Table.ExpandRecordColumn(
#"Expanded Column1", 
"Column1.developer", 
{"name"}, 
{"Column1.developer.name"}
)
in
#"Expanded Column1.developer"

2. The QUERY named "GetAllData" in AE view to pull ALL pages recursively:

  let
  Source = List.Generate(
    () => [Result = try GetData(1) otherwise null, page = 1],
    each [Result] <> null,
    each [Result = try GetData([page] + 1) otherwise null, page = [page] + 1],
    each [Result]
  ),
  #"Converted to Table" = Table.FromList(
    Source,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Expanded Column1" = Table.ExpandTableColumn(
    #"Converted to Table",
    "Column1",
    {
      "Column1.developer",
      "Column1.status",
      "Column1.language",
      "Column1.completed",
      "Column1.started",
      "Column1.deadline",
      "Column1.accuracy",
      "Column1.passing_grade",
      "Column1.pass_status",
      "Column1._id",
      "Column1._assessment",
      "Column1.name",
      "Column1.challenges",
      "Column1.time_spent_ms",
      "Column1.completed_in",
      "Column1.progress",
      "Column1.score"
    },
    {
      "Column1.developer",
      "Column1.status",
      "Column1.language",
      "Column1.completed",
      "Column1.started",
      "Column1.deadline",
      "Column1.accuracy",
      "Column1.passing_grade",
      "Column1.pass_status",
      "Column1._id",
      "Column1._assessment",
      "Column1.name",
      "Column1.challenges",
      "Column1.time_spent_ms",
      "Column1.completed_in",
      "Column1.progress",
      "Column1.score"
    }
  )
in
  #"Expanded Column1"

3. The QUERY named "GetSomeData" in AE view to pull a number (4 in this case) of pages:

let
  GetSomeData = List.Generate(
    () => [page = 1, assessments = GetData(page)], 
    each [page] < 5, 
    each [page = [page] + 1, assessments = GetData(page)]
  ), 
  #"Converted to Table" = Table.FromList(
    GetSomeData, 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ), 
  #"Expanded Column1" = Table.ExpandRecordColumn(
    #"Converted to Table", 
    "Column1", 
    {"page", "assessments"}, 
    {"page", "assessments"}
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1", {"page"}), 
  #"Expanded assessments" = Table.ExpandTableColumn(
    #"Removed Columns", 
    "assessments", 
    {
      "Column1.developer.name", 
      "Column1.status", 
      "Column1.language", 
      "Column1.completed", 
      "Column1.started", 
      "Column1.deadline", 
      "Column1.time_spent_ms", 
      "Column1.completed_in", 
      "Column1.progress", 
      "Column1.score", 
      "Column1.accuracy", 
      "Column1.passing_grade", 
      "Column1.pass_status", 
      "Column1._id", 
      "Column1._assessment", 
      "Column1.name", 
      "Column1.challenges"
    }, 
    {
      "Column1.developer.name", 
      "Column1.status", 
      "Column1.language", 
      "Column1.completed", 
      "Column1.started", 
      "Column1.deadline", 
      "Column1.time_spent_ms", 
      "Column1.completed_in", 
      "Column1.progress", 
      "Column1.score", 
      "Column1.accuracy", 
      "Column1.passing_grade", 
      "Column1.pass_status", 
      "Column1._id", 
      "Column1._assessment", 
      "Column1.name", 
      "Column1.challenges"
    }
  )
in
  #"Expanded assessments"

Related Links:

How to enable API access

Menu

Step 1: Authentication

Step 2: Retrieving Data

Step 3: Pagination (Do-While Loop)

How to wrap a 'do-while' loop around the query:

Step 4: Creating Reports and visualisations

Working Example

Comments

Menu

Step 1: Authentication

Step 2: Retrieving Data

Step 3: Pagination (Do-While Loop)

How to wrap a 'do-while' loop around the query:

Step 4: Creating Reports and visualisations

Working Example

Related articles