Using MS Excel Power Query for manipulating and extracting data from XML or JSON

[alexmohora]

Hello everyone,

I've discovered that MS Excel is quite capable in manipulating data form XML, JSON. There is a feature named "Power Query Editor" in the DATA menu. This editor can be used as a wizard to browse a Json, extract certain data and put it in a table format or even can be used with some sort of scripting for more flexibility.

For example, I've used it to extract the code lists ("codes" and "values") in a specific language. I was able to process the whole folder with code lists but had errors on some files because of different structure.

Please see the attached excel file as an example of extracting the values from a specific codelist.

Alex Mohora

Example_direct-award-justification_RO.xlsx