Error with importing json files from a folder with MoreQuerry

[inavas]

Hi everyone!

I'm working in MacOs and I have download the MoreQuery extension because I have to import several json files into an Excel data sheet, I'm trying to import them from folder, but when I click the button for combine the files I get this error:

"[Expression.Error] No se puede convertir un valor de tipo Record al tipo Table.
Detalles
isRecoverable: True
isExpected: True"

(It means that it can't convert the Record type value into table type)

If I press the more details button, this is what I get:

---------- Mensaje ----------
[Expression.Error] No se puede convertir un valor de tipo Record al tipo Table.

---------- Id. de sesión ----------
7fb4c486-8054-44dc-b64c-71a480fbfe02

---- Removed the rest of the log content since it contained path names.The short error message already explains the problem. ----

I think that I'm missing something. Should I do some set up? How can I import those json files using MoreQuery?

Thanks a lot!

 

[smozgur]

As I can see, the Expand feature doesn't work well with the JSON data on Mac.

So, when you click on the double arrow, it starts sampling the data but the autogenerated "Transform Sample File" query that is used to extract data from all JSON files doesn't return a table but record. It is currently generated as below:

This is wrong. It should return a table with the code below:

So, it is how that this "undocumented" but existing feature works wrong on Mac. The Power Query Developer Team should have forgotten to include this extra line to convert the record to a table. I always wonder why they wouldn't simply use the same procedure as it is on Windows by just excluding the platform dependent functions, but no, they just rewrite everything for Mac.

However, you can do the following to fix this problem manually.

When you are done expanding the column, and get the error you mentioned, edit the Transform Sample File query in the Advanced Editor and copy and paste the following code instead (basically return a table from the JSON record):

let
    Source = Json.Document(Parameter),
    #"Converted to Table1" = Record.ToTable(Source)
in
    #"Converted to Table1"

The actual code then will work without problem and you can continue transforming data.

Note: #"Converted to Table1" is just an identifier name that I copied and paste from Windows above. You can use anything else that doesn't conflict with another query or identifier.

 

[inavas]

Thanks so much! This is working good.

Last question, when I import the json and it have some arrays, they appear as

• in the excel file, how can I access to that values?
  
  Thanks!

 

[smozgur]

It all depends on your JSON file structure. Let's say you have the following JSON files.

file1.json

{
"people": [
  {
    "firstName": "Joe",
    "lastName": "Jackson",
    "gender": "male",
    "age": 28,
    "phone": [
      {
        "name": "Home",
        "number": "1234567890"
      },
      {
        "name": "Work",
        "number": "1234567891"
      }
    ]
  },
  {
    "firstName": "James",
    "lastName": "Smith",
    "gender": "male",
    "age": 32,
    "phone": [
      {
        "name": "Home",
        "number": "1234567892"
      }
    ]
  },
  {
    "firstName": "Emily",
    "lastName": "Jones",
    "gender": "female",
    "age": 24,
    "phone": [
      {
        "name": "Mobile",
        "number": "1234567893"
      },
      {
        "name": "Work",
        "number": "1234567894"
      }
    ]
  }
]
}

file2.json

{
  "people": [
    {
      "firstName": "John",
      "lastName": "Smıth",
      "gender": "male",
      "age": 28,
      "phone": [
        {
          "name": "Home",
          "number": "1234567895"
        }
      ]
    },
    {
      "firstName": "Emıly",
      "lastName": "Jones",
      "gender": "female",
      "age": 22,
      "phone": [
        {
          "name": "Mobile",
          "number": "1234567896"
        },
        {
          "name": "Home",
          "number": "1234567897"
        }
      ]
    }
  ]
}

In this case, we have a root record item called as "people" and it has individual records with properties. Additionally, there is a "phone" property that can contain multiple record items (more than one phone number for each individual).

In this case, we get the following result after expanding the table column:

Each list element represents multiple individual records coming from different JSON files. The first step is expanding the Value column, so each individual record is expanded as a new row.

* The first column, Name, has no use and could be even removed at the first step.

Then we can expand the record column by selecting the properties (fields) that we would like to include into our report.

Now we are getting somewhere. I assume your question is about the phone column here. Each item in this column is a list containing phone numbers, basically it is an array.

If you extract this list column and then records afterwards, you'll get the following result but it will create duplicate records for each person having multiple phone numbers.

Now, you'll need to decide how you want your report is displayed. Perhaps filtering only Work phone numbers that will make sure single record for each person, or get the phone numbers into the new columns which will require more advanced transformation.

In any case at this point, it is a completely new subject and you should post a new question with the sample data you'd like to transform.