JSON Data & Power Query: GOAT Data Wrangling

JSON Data API Power Query - How to Market Your Goat
Reading Time: 4 minutes

You have GOAT customer and prospect data and you are receiving your data via API. But your data is in these weird files called JSON. How exactly do you wrangle your GOAT data in the fastest and easiest way possible so that you can get what you need from it?  Would it be better if we told you it was also low to no cost?  Yes, the light at the end of the tunnel is here. JSON data is easy to use and download, if you know how…

Regardless of business size, you likely have access to Excel.  If you don’t, sorry, this article may not be for you.  If you do, you are in luck!  There is no need to purchase huge data storage, download tools that are not approved by your company, nor bang your head against the wall (Seriously, that just hurts!).  There is an easy way to figure out how to get inside these JSON data files so that you can organize your GOAT data and get a sense of what your ROI really is.  Google, Microsoft, Amazon and many others offer data storage space today that is cheaper than ever before, and sometimes even free.  And, if you are a small business or a freelancer, your laptop storage will likely due nicely.  So, how do we get to JSON and make it work?!?!?


And, the answer is… Excel Power Query 

This tool within your friendly Excel neighbor has got to be one of the most powerful, and least marketed, tool out there.  So, use it safely and widely.  It will help you with not only JSON data files, but with any standard TXT, CSV, and other file types out there.

Here are the instructions of how to create a simple task in Excel Power Query in order to automatically upload multiple JSON data files at once into your Excel workbook.  The screenshots below are from Excel Office 365 and should work similarly to older versions. But you will get the gist.  

But first, some definitions…


JSON, API, Power Query – Oh My!

  • JSON

JavaScript Object Notation is a text file with many levels of data. If you think of how a database works, where you could have one flat table (not efficient at all) or many tables that relate to each other somehow (thus, a relational database), JSON is the “flattening” of a relational database record.  Think of looking at a Rubik’s cube, which lives in a 3-dimensional world, but flattening it so that it looks like a piece of paper, or a 2-dimensional world.  The 2D version of your Rubik’s cube is JSON.   In data terms, specifically here your customers and prospects, it is the “flattening” of your customer private information (name, address, credit card information, etc), purchase information (items purchased, amount paid, date purchased, reviews, etc), customer demographics (household income, age, gender), and so on.  In other words, JSON is your data analytics BFF.

  • API

Application Program Interface is a way in which your systems can talk to other systems in a safe and productive manner. APIs allow anyone outside (with permission, of course.  No mean bots!) a specific system to interface with it in order to augment programming efficiencies or download data.  FUN FACT: Most APIs output data in JSON format.

  • Power Query

By far, one of the coolest Excel tools invented since Excel came into the market in the 1990s. No need to understand macros or VBA (Visual Basic for Applications programming language). All you need to do is to click on the right places on your screen.  BONUS – We will show you how!


15 steps to Power Quering your JSON data:

Please note: column headings and data are shaded out due client privacy and compliance. 

1. Click on the Data tab of your Excel screen (2016, 365, or later). Then click on Get Data / From File / From Folder:

JSON Data Download Step 1 - How to Market Your Goat

2. Select the folder where the data exists. You can also pull data directly from different databases, online services, or other sources. Then click on “OK”:

JSON Data Download Step 2 - How to Market Your Goat

3. Power Query will pull all the files as per below. Click on “Edit”:

JSON Data Download Step 3 - How to Market Your Goat

4. Right click on the header of the column with the binary information (listed here in green). Select “Transform”, then “JSON”:

JSON Data Download Step 4 - How to Market Your Goat

5. Each row from step 4 has been transformed from a multilevel JSON data into a flat file. Each file is called a “Record”:

JSON Data Download Step 5 - How to Market Your Goat

6. To see the information in each Record, or file, click on the double arrow on the “Content” column header. This will allow you to see the data in each file:

JSON Data Download Step 6 - How to Market Your Goat

7. The JSON data now expands into each column below. Any column with double arrows can be expanded further:

JSON Data Download Step 7 - How to Market Your Goat

8. Each double arrow column can be expanded in 2 ways:

  • Expand to New Rows – this will add rows for every value within the record for this specific column. The information on all other columns will repeat across the newly inserted rows
  • Extract Values – this option will put all values within the same cell with a delimiter (see step 13)
JSON Data Download Step 8 - How to Market Your Goat

9. By using step 8.a, we now have multiple rows created for the same file:

JSON Data Download Step 9 - How to Market Your Goat

10. Expand the rows further by clicking on the double arrow as needed:

JSON Data Download Step 10 - How to Market Your Goat

11. The Content.priv4 column has been expanded into several columns

JSON Data Download Step 11 - How to Market Your Goat

12. Expand data values within a column by adding delimiters. Here, we select the “Extract Values” option and use a delimiter to include all values within the cell:

JSON Data Download Step 12 - How to Market Your Goat

13. A double pipe ( || ) is used to separate the values:

JSON Data Download Step 13 - How to Market Your Goat

14. All values within Content.priv4.D6 are now double pipe delimited, and remain within the same column without adding new rows:

JSON Data Download Step 14 - How to Market Your Goat

15. Click on “Close & Load”. This will export the data into your Excel spreadsheet:

JSON Data Download Step 15 - How to Market Your Goat

DONE!  It’s simple if you know where to click.


Need Help Wrangling your Marketing Data – We can help!

If you have a GOAT or just about anything to sell but don’t have the time to organize your Marketing Data, let Key Paragon help you. We are a Marketing Solutions and Advisory firm. We partner with our clients to address their most important marketing challenges, whether it be branding, strategy, process automation, social media, analytics, or marketing technology. Happy GOAT marketing!

Contact us today and we will build you the Greatest of All Time Marketing Database, Analytics, and Reporting!

Want to know more about How to Market Your Goat?  Take a look at our other blogs here.


Our Contributor:

Erika is a data and targeting expert.  She has developed many segment analysis processes and target populations in order to support business initiatives.  Her passion in chaos theory and fractal analysis helps her view data in a very unique way.

Erika enjoys also reading about the future of work and how we can bring it closer to the present.  “How we work today will be so different in 10/20 years from now.  It’s exciting to see what will happen given all the economic, human, and technical evolution we are currently experiencing.”

…and Han shot first…

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *