Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to import all columns from a JSON when not all the objects have the same keys

How to import something like this? Cannot expand on 'ciudad', since it's not present in all objects. I'm using Power Query in MS Excel for desktop.   [ { "nombre": "Juan", "edad": 30, "ciudad": ...
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    Anonymous , 

    Here are a few steps you can follow.

    1. Load JSON: Go to Data > Get Data > From File > From JSON and load your file.

    2. Convert to Table: Click To Table in Power Query.

    3. Expand Columns: Use the Expand Column button (next to the column name). Missing keys will appear as null.

    4. Handle Missing Keys: If a key (e.g., ciudad) is not in all objects, it will still create a column with null for missing values.

    5. Load to Excel: Click Close & Load to return the data to Excel.

    This ensures all potential columns are imported, including those with missing keys.

OwenAuger's avatar
OwenAuger
Super User
1 year ago

Hi Anonymous 

Here's how I would set up the query:

let

  // Connect to JSON file
  SourceJSON =
    "[
       { ""nombre"": ""Juan"", ""edad"": 30, ""ciudad"": ""Madrid"" },
       { ""nombre"": ""Ana"", ""edad"": 25, ""ciudad"": null },
       { ""nombre"": ""Luis"", ""edad"": 40 }
     ]",
  #"JSON to Records" = Json.Document(SourceJSON),
  #"Records to Table" = Table.FromRecords(
    #"JSON to Records",
    type table [nombre = nullable text, edad = nullable Int64.Type, ciudad = nullable text],
    MissingField.UseNull
  )
in
  #"Records to Table"

 

Replace the first step as required.

Does this work for you?