{"id":388,"date":"2017-08-28T12:20:13","date_gmt":"2017-08-28T02:20:13","guid":{"rendered":"https:\/\/www.dynamicwebtraining.com.au\/blog\/?p=388"},"modified":"2024-11-14T14:25:54","modified_gmt":"2024-11-14T03:25:54","slug":"using-get-transform-microsoft-excel-2016","status":"publish","type":"post","link":"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016","title":{"rendered":"Using Get &#038; Transform in Microsoft Excel 2016"},"content":{"rendered":"\n<p><strong>Get &amp; Transform<\/strong> is a powerful data analysis tool in Microsoft Excel that allows you to import, refine and work with data from a range of sources, including files saved to your computer and web pages on the internet. You can shape data in order to display the details relevant to your purposes and hide information that is not required, without making permanent and irreversible changes to the data.<\/p>\n\n\n\n<!--more-->\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\"><div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"609\" height=\"231\" src=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/08\/get-and-transform.png\" alt=\"Get and Transform in Excel - Dynamic Web Training\" class=\"wp-image-390\" srcset=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/08\/get-and-transform.png 609w, https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/08\/get-and-transform-300x114.png 300w\" sizes=\"auto, (max-width: 609px) 100vw, 609px\" \/><figcaption class=\"wp-element-caption\">Click image to enlarge&nbsp;<i class=\"fa fa-external-link\"><\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n<h2 class=\"wp-block-heading p1\">Using Get &amp; Transform<\/h2>\n\n\n\n<p>The process of using <strong>Get &amp; Transform<\/strong> consists of three general steps: <strong>discover<\/strong>, <strong>combine<\/strong> and <strong>refine<\/strong>, and <strong>load and share<\/strong>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Discover \u2013<\/strong> The first step in working with <strong>Get &amp; Transform<\/strong> is finding and importing the required data. You can use data from sources on your computer including files (e.g. Excel workbooks and text files) and databases (e.g. Access, SQL and Oracle databases); private data that is stored on servers or in the cloud, such as files stored in SharePoint, Azure and Salesforce; and publicly available data from websites such as Wikipedia and Gapminder. You can even use data from your organisation\u2019s Facebook page.<br>Once you have located the data you require, you can import it using the relevant option on the <strong>New Query<\/strong> menu on the <strong>Data<\/strong> tab in Excel. You can choose to select only the data you need from the data source before importing it instead of having to import it all.<\/li>\n\n\n\n<li><strong>Combine and Refine \u2013<\/strong> Once you have imported the required data, you can begin to work with it in the <strong>Query Editor<\/strong> to display the details that you need. The <strong>Query Editor<\/strong> is a separate window that allows you to modify how data is displayed without making permanent changes to the data itself. For example, if you remove columns in the <strong>Query Editor<\/strong> and display the data in Excel, it will appear as though the removed columns never existed; however, you can then return to the <strong>Query Editor<\/strong> and undo the column removal to display the columns again. Performing the same task in Excel would involve either hiding the columns, making the data confusing to work with, or permanently deleting the columns which results in the loss of data that you may need later on.<\/li>\n<\/ul>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\"><div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"995\" height=\"641\" src=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/08\/query-editor.png\" alt=\"Query Editor in Excel 2016 - Dynamic Web Training\" class=\"wp-image-391\" srcset=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/08\/query-editor.png 995w, https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/08\/query-editor-300x193.png 300w\" sizes=\"auto, (max-width: 995px) 100vw, 995px\" \/><figcaption class=\"wp-element-caption\">Click image to enlarge&nbsp;<i class=\"fa fa-external-link\"><\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n<p>You can also use the <strong>Query Editor<\/strong> to <strong>merge<\/strong> queries \u2013 that is, if you have imported more than one set of data into Excel to work with, you can combine them in the <strong>Query Editor<\/strong>. This is useful for comparing data side-by-side; for example, for displaying sales data against advertising and marketing information. To merge queries, there must be a column that is identical (or close to identical) in both or all of the queries to be merged. This is usually a column that provides information on how to read the data \u2013 for example, if you were comparing sales and advertising data, the shared column might provide a time frame such as years or month names.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Load and Share<\/strong> \u2013 The final step is to <strong>Load<\/strong> the data you have refined to Excel and share it with others. Once you have made the necessary changes to the data set in the <strong>Query Editor<\/strong>, you can load it to the Excel workbook into which it was originally imported and proceed to work with it in other ways (e.g. make aesthetic changes such as formatting the text, create charts and so on). You do not have to wait until you are completely finished with shaping and refining data to load it to Excel \u2013 you can open the <strong>Query Editor<\/strong> and make modifications any time you like.<\/li>\n<\/ul>\n\n\n\n<p>When you are finished working on your workbook, you can share it in the same ways in which you would share an ordinary Excel workbook \u2013 by email, cloud sharing, presenting and so on.<\/p>\n\n\n\n<h3 class=\"wp-block-heading p1\">Why Use Get &amp; Transform?<\/h3>\n\n\n\n<p>Get &amp; Transform is most useful when working with large data sets. When you import a large amount of data, it is unlikely that you will need all of it for your final report or presentation \u2013 especially when a data set includes hundreds or thousands of rows and\/or columns. <strong>Get &amp; Transform<\/strong> is an effective way of working with large amounts of data without compromising its integrity. It is also very handy for pulling together data from different files or sources to combine it and view comparisons or correlations.<\/p>\n\n\n\n<p>For recent updates to the Get &amp; Transform Add-ins refer to this <a href=\"https:\/\/blogs.office.com\/en-us\/2016\/05\/23\/may-2016-updates-for-get-transform-in-excel-2016-and-the-power-query-add-in\/\" target=\"_blank\" rel=\"nofollow external noopener\">article<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Get &amp; Transform is a powerful data analysis tool in Microsoft Excel that allows you to import, refine and work with data from a range of sources, including files saved to your computer and web pages on the internet. You can shape data in order to display the details relevant to your purposes and hide&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-388","post","type-post","status-publish","format-standard","hentry","category-microsoft-excel"],"yoast_head":"<title>Get &amp; Transform Tool in Excel 2016 | Dynamic Web Training<\/title>\n<meta name=\"description\" content=\"View and Share Dynamic Web Training Blog Archives. This is a great source of articles and posts on Computer and IT training, tutorials and insights\" \/>\n<meta name=\"robots\" content=\"index, follow\" \/>\n<meta name=\"googlebot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta name=\"bingbot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Get &amp; Transform Tool in Excel 2016 | Dynamic Web Training\" \/>\n<meta property=\"og:description\" content=\"Understand the uses of the powerful data analysis tool in Excel 2016 called the Get &amp; Transform. Display the relevant details from a range of data sources\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016\" \/>\n<meta property=\"og:site_name\" content=\"Dynamic Web Training Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-28T02:20:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-14T03:25:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/08\/get-and-transform.png\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:creator\" content=\"@dynamicwebtrain\" \/>\n<meta name=\"twitter:site\" content=\"@dynamicwebtrain\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\",\"name\":\"Dynamic Web Training\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/\",\"sameAs\":[\"https:\/\/www.facebook.com\/DynamicWebTraining\/\",\"https:\/\/www.linkedin.com\/company\/dynamic-web-training\",\"https:\/\/twitter.com\/dynamicwebtrain\"],\"logo\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#logo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2016\/02\/logo.png\",\"width\":361,\"height\":109,\"caption\":\"Dynamic Web Training\"},\"image\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#logo\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/\",\"name\":\"Dynamic Web Training Blog\",\"description\":\"The Ultimate Training Experience.\",\"publisher\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/08\/get-and-transform.png\",\"width\":609,\"height\":231},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#webpage\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016\",\"name\":\"Get & Transform Tool in Excel 2016 | Dynamic Web Training\",\"isPartOf\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#primaryimage\"},\"datePublished\":\"2017-08-28T02:20:13+00:00\",\"dateModified\":\"2024-11-14T03:25:54+00:00\",\"description\":\"Understand the uses of the powerful data analysis tool in Excel 2016 called the Get & Transform. Display the relevant details from a range of data sources\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\",\"name\":\"Training Blog\"}},{\"@type\":\"ListItem\",\"position\":2,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\",\"name\":\"Training\"}},{\"@type\":\"ListItem\",\"position\":3,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel\",\"name\":\"Microsoft Excel\"}},{\"@type\":\"ListItem\",\"position\":4,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016\",\"name\":\"Using Get &#038; Transform in Microsoft Excel 2016\"}}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#webpage\"},\"author\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233\"},\"headline\":\"Using Get &#038; Transform in Microsoft Excel 2016\",\"datePublished\":\"2017-08-28T02:20:13+00:00\",\"dateModified\":\"2024-11-14T03:25:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#webpage\"},\"publisher\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/using-get-transform-microsoft-excel-2016#primaryimage\"},\"articleSection\":\"Microsoft Excel\",\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233\",\"name\":\"Dynamic Web Training\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g\",\"caption\":\"Dynamic Web Training\"},\"description\":\"Dynamic Web Training is Australia's leading provider of instructor led software training. We offer training courses in Adobe, Web Design, Graphic Design, Photoshop, InDesign, Dreamweaver and many more.\",\"sameAs\":[\"https:\/\/www.facebook.com\/DynamicWebTraining\/\",\"https:\/\/www.linkedin.com\/company\/dynamic-web-training\",\"https:\/\/twitter.com\/dynamicwebtrain\"]}]}<\/script>","_links":{"self":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/388","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/comments?post=388"}],"version-history":[{"count":10,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/388\/revisions"}],"predecessor-version":[{"id":1976,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/388\/revisions\/1976"}],"wp:attachment":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/media?parent=388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/categories?post=388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/tags?post=388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}