Split json data to columns in excel

Question

I have a column with JSON data inside. The fields inside are not consistent so I cannot use text-to-column

{'gender': 'Female', 'nationality': 'ESP', 'document_type': 'passport', 'date_of_expiry': '2021-01-25', 'issuing_country': 'ESP'}

{'document_type': 'driving_licence', 'date_of_expiry': '2056-03-07', 'issuing_country': 'GRC'}

How do I get this into columns such as gender, nationality, document_type etc.

Thanks

Answer 1

Just to clarify, does one of the columns in your Excel spreadsheet contain JSON encoded data? If so, it is fairly easy to transform using PowerQuery.

Go to the sheet containing your data, and select Data, From Table/Range .

This should select the sheet as a Range of data for further transformation and load it into the PowerQuery Editor.

Click the Column header of the column that contains the JSON encoded data. This will select the column. Right mouse click and select Transform, JSON

PowerQuery recognises that each cell contains a JSON Record and will convert the data to a Record.

Now you can choose which columns you would like to display in your table. Here, I only want the one that has ObjectID.

Now you will have a column (or columns if you selected more than one) with the seperated data from the JSON Record. You can use the Choose Columns or Remove Columns to from the main toolbar to further refine your view.

Once you are happy, just Close and Load to use the data to a new Excel Sheet.

PowerQuery is extremely powerful for this type of data manipulation and is well worth spending a little time to learn.