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

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.

4 Replies

  • 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?

  • rohit1991's avatar
    rohit1991
    Super User
    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.

  • v-priyankata's avatar
    v-priyankata
    Community Support
    1 year ago

    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.

  • v-priyankata's avatar
    v-priyankata
    Community Support
    1 year ago

    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.