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

[Anonymous]

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": "Madrid" },
{ "nombre": "Ana", "edad": 25, "ciudad": null },
{ "nombre": "Luis", "edad": 40 }
]

[v-priyankata]

Hi @Anonymous 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @Anonymous 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[rohit1991 · Accepted solution]

@Anonymous ,  here are a few step s 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]

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?