Power QueryExercises

Turn a JSON list of accounts into a table (saas)

A JSON file holds a list of flat account records. Convert it to a table with one row per account.

Level
beginner
Time
~5 min
Task
Build the query
Access
Free — no account needed
Loading the practice workspace…

About this exercise

What you'll practise

Parse JSON and convert a list of records into a table, keeping JSON numbers and true/false as typed values.

Ledgerly (fictional) exports its customer accounts from the billing service as JSON each night.

Steps

  1. Parse AccountsJson with Json.Document. The result is a list of records with fields id, name, plan, seats and active.
  2. Convert it into a table with one row per record and those five columns.
  3. Keep seats as numbers and active as true/false values (don't convert them to text).

Your result must

  • One row per account
  • Columns: id, name, plan, seats, active
  • seats are numbers, active is true/false

Input

  • AccountsJson (file)
    • JSON document (parse with Json.Document)

Expected output columns

id (text), name (text), plan (text), seats (number), active (logical)

First 3 of 6 expected rows
idnameplanseatsactive
LG-0001Zane MensahTeam10true
LG-0002Rosa VargaTeam7true
LG-0003Tariq HaddadStarter12false