Clean JSON Data in Excel

When working with data, it is important to know tips and tricks that will save you a lot of time. As a developer, it is imperative to harness the power of algorithmic thinking, meaning that, it really does not matter if you spend eternity developing something that someone has already developed. If there is a way to use what someone else has developed to accomplish what you want, go ahead and use it.


When cleaning data, it is very helpful to use Microsoft Excel to put data in a format you want. This process is called Transforming, for example, you have JSON data that is in JSON objects and shows up as a list and you want that list of object to show up as a table so that you can import the data into a database table.

Solution:

  1. Get data from web API using Data tab, from the web and paste API
  2. Excel Power Query will open a window and JSON data shows lists of objects that you want to be columns
  3. On the right-hand side, find [Into Table] button and it will make the list of the object into a table
  4. In the newly created table, find expand button on the column row (it has two arrows pointing different ways) and click it
  5. The new window pops up with all data that was in list objects as the independent.
  6. You can load the data into a table in Excel and import it into Datawarehouse