![excel query table tutorial excel query table tutorial](https://radacad.com/wp-content/uploads/2021/06/2021-06-14_13h59_45.png)
![excel query table tutorial excel query table tutorial](https://radacad.com/ssis/PowerQueryIntro/2.png)
Uncheck the Use original column name as prefix box, then click OK. On the Privacy Levels window, check the box to Ignore Privacy Levels, then click Save.Ĭlick the expand icon at the top of the Imported FX Rates column (the new column we just added). In the Custom Column window, enter the following: In the query list it shows the FX Rate query we have just changed with an fx next to it, this means it is a function. The Power Query window will change to look like this: = Table.TransformColumnTypes(Data0,)Ĭlick Done in the Advanced Editor window to accept the changes.
#EXCEL QUERY TABLE TUTORIAL CODE#
Looking at the M code in the Formula Bar reveals the issue there are specific references to the USD currency in the header (see the red highlighted sections below). The Changed Type step will reveal the following error. The following M code: = Web.Page(Web.Contents(" USD &date= "))īecomes = Web.Page(Web.Contents(" EUR&date= "))Ĭlick through the remaining steps of the query. For our example, I’ve selected EUR and 01 January 2019. Edit the M code in the formula bar to change the query to include a different currency and a different date. The Power Query editor will open again, click on the Source step within the Applied Steps. In Excel click Data -> Queries and Connectionsĭouble click the query in the Queries and Connections pane to open the Power Query editor. Now let’s edit the query to use it for different dates and currencies (in doing this, we will come across an error which we need to fix we can handle it).
#EXCEL QUERY TABLE TUTORIAL UPDATE#
If the webpage contains live data, it will update the query with that live data each time we click refresh. Give the query a useful name, such as FXRatesĬlick Home -> Close and Load to push the data into Excel. When the Power Query Editor loads it contains all the currency data. Once you have found the right table, click Transform Data. We must click through each until we find the one we want. As web tables from websites can have poor naming conventions, it’s not always clear which table we need. The next window to appear will be the Navigator window. Paste the URL we copied earlier into the URL box and click OK. Copy the URL, then in Excel click Data -> Get Data -> From Other Sources -> From Web Hmmm… I wonder if we could use that in a more advanced way? USD&date= Īnyway, before getting onto the more advanced, let’s start with the basics. If you look closely, it contains the currency code and the date within the URL. The most useful part of the screenshot above is the URL. The website contains pages which display the exchange rates for any currency on any date. Basic web importįor the purpose of this exercise, we will be using to import exchange rates into Power Query. Power Query can do a lot, but the website has to be in a reasonably usable format to begin with.Īnyway, enough talk, time to import some data from the web. While you might get excited at the thought of absorbing data from everywhere with the click of a button, it’s not a silver bullet. However, just because the data is there does not make it easy to get at In this post, we’ll look at how we can load web data into Power Query. The web is a vast place with so much information.