{"id":4441,"date":"2016-09-15T23:48:41","date_gmt":"2016-09-15T21:48:41","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4441"},"modified":"2024-02-06T16:08:58","modified_gmt":"2024-02-06T15:08:58","slug":"import-web-data-using-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/import-web-data-using-power-query\/","title":{"rendered":"Import Web Data Using Power Query"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/installing-power-query-excel-2010\/\" target=\"_blank\" rel=\"noopener noreferrer\">Power Query<\/a> lets you clean and transform your raw &amp; messed up\u00a0data into a format\u00a0where you can do further Excel analysis with ease.<\/p>\n<p>Firstly, we would need data to play with <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/50-things-you-can-do-with-excel-power-query\/\" target=\"_blank\" rel=\"noopener\" title=\"Power Query\" data-wpil-keyword-link=\"linked\">Power Query<\/a>, right? \u00a0The good thing with Power Query is that there is a\u00a0<strong>multitude of ways to pull in\u00a0data.<\/strong><\/p>\n<p>One ways is to\u00a0get data from the internet by using the\u00a0<strong>importing web data <\/strong>feature.<strong>\u00a0\u00a0<\/strong>I will show you\u00a0how we can parse a webpage and use it for Power Query!<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/Sv5POlAAId4?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-1.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-1.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"> download excel workbook <i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Import-Web-Data-1.xlsx<\/span><\/a><\/p>\n<\/div>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/install-power-query-with-excel-2013\/\" target=\"_blank\" rel=\"noopener noreferrer\">Click to see tutorial on how to install Power Query in Excel 2013<\/a><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/installing-power-query-excel-2010\/\" target=\"_blank\" rel=\"noopener noreferrer\">Click to see tutorial on how to install Power Query in Excel 2010<\/a><\/p>\n<p>In Excel 2016 it comes built in the Ribbon menu under the <strong>Data<\/strong> tab and within the <strong><a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/50-things-you-can-do-with-excel-power-query\/\" target=\"_blank\" rel=\"noopener\" title=\"Get &amp; Transform\" data-wpil-keyword-link=\"linked\">Get &amp; Transform<\/a><\/strong> group.<\/p>\n<p>&nbsp;<\/p>\n<p>Let\u2019s go through the steps in detail using Excel 2016 as our workbook:<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1<\/strong>:<\/span>\u00a0Go to <strong>Data &gt; New Query &gt; From Other Sources &gt; From Web.\u00a0<\/strong><\/p>\n<p>(In Excel 2010 &amp; 2013 you need to go to <strong>Power Query &gt; From Web<\/strong>)<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4446\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-01.png\" alt=\"Import Web Data Using Power Query\" width=\"575\" height=\"709\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-01.png 575w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-01-243x300.png 243w\" sizes=\"(max-width: 575px) 100vw, 575px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2<\/strong>:<\/span>\u00a0This will ask for a website where you want to get the data from.<\/p>\n<p>We want to get the stock market indexes from Google finance.<\/p>\n<p>Copy and paste this\u00a0URL: <strong>https:\/\/www.google.com\/finance<\/strong><\/p>\n<p>Press <strong>OK<\/strong> to continue<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4447\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-02.png\" alt=\"Import Web Data Using Power Query\" width=\"699\" height=\"249\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-02.png 699w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-02-300x107.png 300w\" sizes=\"(max-width: 699px) 100vw, 699px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span>\u00a0<\/strong>Power Query\u00a0will now try to parse <a href=\"https:\/\/www.google.com\/finance\" target=\"_blank\" rel=\"noopener noreferrer nofollow\">this webpage<\/a>, and in doing so, has determined that there are multiple tables of data in there.<\/p>\n<p>Here is what Power Query brings back as Table options:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/parse-web.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4503\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/parse-web.jpg\" alt=\"Import Web Data Using Power Query\" width=\"330\" height=\"878\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/parse-web.jpg 330w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/parse-web-113x300.jpg 113w\" sizes=\"(max-width: 330px) 100vw, 330px\" \/><\/a><\/p>\n<p>By clicking on the Table options in the <strong>Navigator<\/strong> pane, you will get a preview of the web data on the right hand side.<\/p>\n<p>Let&#8217;s select <strong>Table 0<\/strong> for our example and press <strong>Edit<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4448\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-03.png\" alt=\"Import Web Data Using Power Query\" width=\"879\" height=\"699\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-03.png 879w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-03-300x239.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-03-768x611.png 768w\" sizes=\"(max-width: 879px) 100vw, 879px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4:<\/span>\u00a0<\/strong>The data is now imported into the <strong>Query Editor<\/strong> window.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4449\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-04.png\" alt=\"Import Web Data Using Power Query\" width=\"479\" height=\"728\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-04.png 479w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-04-197x300.png 197w\" sizes=\"(max-width: 479px) 100vw, 479px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5:<\/span>\u00a0<\/strong>We are going to transform <strong><em>Column3<\/em><\/strong> so the data can be easier to analyze.<\/p>\n<p><strong>Select the <em>Column3<\/em> heading<\/strong> with your mouse<strong>\u00a0<\/strong>and go to <strong>Home &gt;<\/strong>\u00a0<strong>Split Column &gt; By Delimiter.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4450\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-05.png\" alt=\"Import Web Data Using Power Query\" width=\"679\" height=\"653\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-05.png 679w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-05-300x289.png 300w\" sizes=\"(max-width: 679px) 100vw, 679px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong style=\"line-height: 1.5;\"><span style=\"color: #ff0000;\">STEP 6:<\/span>\u00a0<\/strong>We want to\u00a0split the third column into two separate values. \u00a0One for the actual number, and another for the percentage.<\/p>\n<p>To achieve this, we need to split the data\u00a0by the space in\u00a0between the two values.<\/p>\n<p><span style=\"line-height: 1.5;\">Select\u00a0<strong>Space<\/strong>\u00a0from the delimiter drop-down\u00a0and press\u00a0<strong>OK.<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4451\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-06.png\" alt=\"Import Web Data Using Power Query\" width=\"699\" height=\"329\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-06.png 699w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-06-300x141.png 300w\" sizes=\"(max-width: 699px) 100vw, 699px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 7: <\/strong><\/span>\u00a0Our imported web data has been\u00a0transformed into our liking.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-07.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4452\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-07.png\" alt=\"Import Web Data Using Power Query\" width=\"551\" height=\"657\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-07.png 551w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/Import-Web-Data-07-252x300.png 252w\" sizes=\"(max-width: 551px) 100vw, 551px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>We are now ready to load it into our Excel worksheet by selecting\u00a0<strong>Close &amp; Load<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/close-load.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Split First &amp; Last Name Using Power Query\"  class=\"alignnone wp-image-2976 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/close-load.jpg\" alt=\"Split First &amp; Last Name Using Power Query\" width=\"114\" height=\"118\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 8:<\/strong> <\/span>\u00a0Now that the data is in your Excel worksheet, tomorrow all you need to do is <strong>select the data<\/strong> &amp; press the <strong>Refresh<\/strong> button from the<strong> Workbook Queries<\/strong> pane on the right hand side.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/workbookk-queries2.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Import Web Data Using Power Query\"  class=\"alignnone size-full wp-image-4510\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/workbookk-queries2.jpg\" alt=\"Import Web Data Using Power Query\" width=\"751\" height=\"455\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/workbookk-queries2.jpg 751w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/workbookk-queries2-300x182.jpg 300w\" sizes=\"(max-width: 751px) 100vw, 751px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>You will have the latest stock prices &amp; all this\u00a0without copying and pasting from the webpage! Thank You Power Query \ud83d\ude42<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Power Query lets you clean and transform your raw &amp; messed up\u00a0data into a format\u00a0where you can do further Excel analysis with ease. Firstly, we would need data to play with Power Query, right? \u00a0The good thing with Power Query is that there is a\u00a0multitude of ways to pull in\u00a0data. One ways is to\u00a0get [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17753,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[120,121],"tags":[177,117],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4441"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=4441"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4441\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17753"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=4441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=4441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=4441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}