Often you happen to find online a range of data and information that we are interested to import directly into our PowerPivot models .
Of course, the simplest solution would be to write everything by hand , but of course would require much time and effort .
But we can get help from power queries to overcome this problem . at this point , however, we distinguish two types of data :
- Table directly exposed on a website , we think , for example, a table of wikipedia
- Table instead exposes a result deriving makes a json file
We face two types separately
Tabella esposta su sito web
This is the simplest system , for example, take into account the list of nations and its population directly from wikipedia . What we need to do is open an excel workbook and select the web button from power query tab :
At this point you should type the link as it is written in our browser , you will be asked to authenticate, and you must choose the anonymous , since we have no credentials .
At this point it should open the window where you choose the tables , just select the one that we need , in our case countries and dependencies :
After that we can upload it on power pivot or modify it in power query, this image can represent one result:
Often this type of tables are feeded by type of file calls json . this particular object can be read and loaded within power queries. To understand how to recover from a website the json file, I refer to my other article I’ve written lately where I explain how to recover the link json easily.
We start to have already recovered a specific address json (we’ll use this: http://data.huffingtonpost.com/2014/world-cup/matches/731823.json). In this case, however, we could not rely directly adding a web address and the relative import within the data model, but additional steps are needed.
The steps to import the file are always the same, you import a web address from the previously window view, at this point, however, opens directly power query and display the following screen, where for us simply click the tab “in the table” to make the tabular files:
At this point we should have converted the json file into a table , in our case we are in front at 3 different record , you have to explore a bit to understand where the file is what we need , in our case everything is located under the line events , to be able to see the data you need to :
Filter the single row (usually the data are below the line where the value is a list)
2. Expand the list column ad prepare the dataset as you prefer:
At this point you can import the result on excel or on powerpivot