An Excel Web query allows you to bring data from a Web site into an Excel worksheet. It will find any tables on the Web page and let you select the ones containing data you want to put into your worksheet.
To demonstrate the process, we're going to start with a simple Web query using Wikipedia. This is a great example because the data we're interested in is presented in a tabular format.
It is well known that World Wide Web contains huge useful data. However, we have to import the data into Microsoft Excel before doing any kind of analysis. There are two methods that you can use to complete this kind of task. You can do it manually or you can use VBA code. In this blog I’ll show you how to do it without VBA.
URL USED
Choose Advanced option. Place the URL into the field for URL. Command timeout in minutes is optional. You can set this later in Advanced Editor for M language. But we need to give the web page a time to load the document content. So put some value into that field [0.2min]. Inside the Query Editor you can adjust the loading time as well.
My table has couple of columns and rows I won’t need and, I must change some data types and perform a little bit of data cleansing. Next step > Transform Data.
There are couple of actions which need to take a place before this document will be ready to use. Short illustration:
On the right-side panel click on the NAVIGATION step to add a function. This function will dynamically represent the name of our columns.
I’ve created a list of headers. To access them we need to add an index to the function.
Adding an index.
Index {0} is the very first header in the table. Second has value 1. That’s because the indexing starts from zero.
Number of changes have been done in the data set, now I must replace the referencing columns with the function itself. Be careful about the index number which must match the referencing header. I’ve marked the steps where the function should be used.
You can do so directly in the query editor or you can copy and past the M code into a text document a replace it there.
Replace “Deaths 24,883” by Table.ColumnNames(Data1){3}
But this was just an example. You have to do so for all the headers.
Like I said, you must check your APPLYIED STEPS bar on the right-side and replace all to referencing header names with the function.
If you try to use the function in the steps which are not referencing the origin columns you will get an error. In the last example I have just header renamed by myself, these are not referencing the headers from web source. Do not apply the function in these steps.
After you’ve done all these steps you can apply the changes and load the document into excel sheet.
You can use XLOOKUP function to add more columns to it. After refreshing it won’t break, so you can build more complex analysis…
Commentaires