{"id":5432,"date":"2021-11-01T16:00:30","date_gmt":"2021-11-01T16:00:30","guid":{"rendered":"https:\/\/www.stl-training.co.uk\/b\/?p=5432"},"modified":"2024-05-13T10:06:41","modified_gmt":"2024-05-13T10:06:41","slug":"the-magic-of-power-query","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/the-magic-of-power-query\/","title":{"rendered":"The Magic of Power Query"},"content":{"rendered":"<h5><strong>One word That describes Power Query in Excel \u2013 MAGIC!<\/strong><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559738&quot;:40,&quot;335559739&quot;:0,&quot;335559740&quot;:259}\"><strong>\u00a0<\/strong><\/span><\/h5>\n<p><span data-contrast=\"auto\">\u00a0<\/span><span data-contrast=\"auto\"><a href=\"\/syl\/300\/excel-power-query.html\">Power Query<\/a> will completely change the way you work in Excel!<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span>For most Excel users, Power Query could be the tool that reduces time spent in Excel dramatically.<\/p>\n<p><a href=\"https:\/\/corporatefinanceinstitute.com\/resources\/excel\/study\/power-query\/\">Power Query<\/a> could also minimise the use of VBA coding in Excel. It can be seen as a machine. Once the \u201cmachine\u201d is built, it will continuously repeat the tasks for new data added to Excel.<\/p>\n<p>It is a much easier alternative for automating processes than VBA programming because it does not necessitate coding.<span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\"> In this article, we will explore the magic of Power Query.<\/span><\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-7551 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-1.png\" alt=\"The Magic of Power Query\" width=\"1360\" height=\"735\" srcset=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-1.png 1360w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-1-300x162.png 300w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-1-1024x553.png 1024w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-1-768x415.png 768w\" sizes=\"(max-width: 1360px) 100vw, 1360px\" \/><\/p>\n<h5><strong>key features of Power Query<\/strong><\/h5>\n<p>You can:<span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<ul>\n<li><span data-contrast=\"auto\"> Connect live to an external data source<\/span><\/li>\n<li><span data-contrast=\"auto\"> Structure internal and external data<\/span><\/li>\n<li><span data-contrast=\"auto\"> Clean, merge, append, and group internal and external data<\/span><\/li>\n<li><span data-contrast=\"auto\"> Automate tasks<\/span><\/li>\n<li><span data-contrast=\"auto\"> Transfer data from the query connection to the Excel data model<\/span><\/li>\n<\/ul>\n<h5><span data-contrast=\"auto\"><strong>Connecting live to an external data source<\/strong><\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559738&quot;:40,&quot;335559739&quot;:0,&quot;335559740&quot;:259}\"><strong>\u00a0<\/strong><\/span><\/h5>\n<p><img decoding=\"async\" class=\"alignnone wp-image-7552 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-2.png\" alt=\"The Magic of Power Query\" width=\"752\" height=\"129\" srcset=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-2.png 752w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-2-300x51.png 300w\" sizes=\"(max-width: 752px) 100vw, 752px\" \/><\/p>\n<p><span data-contrast=\"auto\">The tools to connect to external data are found on the Data tab in Excel, in the Get &amp; Transform Data group. You can connect to any live data if you have legal access to it. You may need to get help from your IT department to connect to your source, depending on what your source is. When you have created the connection, you can create your reports, analyses, or dashboards and they will auto-update when new data is added to the source.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">You can also connect to folders, which can be extremely useful if you receive your data from clients. If you connect Power Query to a folder, drop any new data into that folder and your report, dashboard, or analysis will update, including the new data.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<h5><span data-contrast=\"auto\"><strong>Structure internal and external data<\/strong><\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559738&quot;:40,&quot;335559739&quot;:0,&quot;335559740&quot;:259}\"><strong>\u00a0<\/strong><\/span><\/h5>\n<p><span data-contrast=\"auto\">Many Excel users struggle with source data that is structured incorrectly. This makes working in Excel much more time-consuming. Excel prefers lists, and all source data should be in a well-structured list. Power Query can restructure your data sets. When you have set up the query, the \u201cmachine\u201d will restructure any newly added data in future. <\/span><\/p>\n<h5><span data-contrast=\"auto\">\u00a0<\/span>The Power Query Editor<\/h5>\n<p><img decoding=\"async\" class=\"alignnone wp-image-7553 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-3.png\" alt=\"The Magic of Power Query\" width=\"752\" height=\"403\" srcset=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-3.png 752w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-3-300x161.png 300w\" sizes=\"(max-width: 752px) 100vw, 752px\" \/><\/p>\n<h5><span data-contrast=\"auto\"><strong>Clean, merge, append, and group internal and external data<\/strong><\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559738&quot;:40,&quot;335559739&quot;:0,&quot;335559740&quot;:259}\"><strong>\u00a0<\/strong><\/span><\/h5>\n<p><span data-contrast=\"auto\">If your data contains US dates, extra spaces, unprintable characters, incorrect spellings, empty rows or columns, incorrect formats, or hundreds of other issues, Power Query is the right tool for you.\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">Power Query can also merge any number of tables. Say goodbye to complicated and memory-heavy lookup and reference functions.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">Connect to many lists and turn them all into one list by using the Append option in Power Query.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">The Group Data tool in Power Query can replace the use of Pivot Tables and the Subtotal tool, but is also particularly useful as a part of the Merge Data tool which generates related keys between the tables.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">All tools in Power Query are available both for external and internal data.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<h5><strong>Automate tasks<\/strong><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559738&quot;:40,&quot;335559739&quot;:0,&quot;335559740&quot;:259}\"><strong>\u00a0<\/strong><\/span><\/h5>\n<p><span data-contrast=\"auto\">Power Query works in much the same way as the macro recorder in Excel. The query records the steps you are doing in your data sets and writes code in a language called Power Query M. <\/span><\/p>\n<h5><span data-contrast=\"auto\">Code example:<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/h5>\n<p><img decoding=\"async\" class=\"alignnone wp-image-7554 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-4.png\" alt=\"The Magic of Power Query\" width=\"752\" height=\"120\" srcset=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-4.png 752w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2021\/11\/Magic-of-Power-Query-4-300x48.png 300w\" sizes=\"(max-width: 752px) 100vw, 752px\" \/><\/p>\n<p><span data-contrast=\"auto\">One of the differences between a macro and steps in Power Query is how the code gets executed. Recorded macros will only run with user input, so you need to execute the code manually. Power Query M steps will automatically execute every time there is a task to do.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<h5><strong>Transfer data from the query connection to the Excel data model<\/strong><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559738&quot;:40,&quot;335559739&quot;:0,&quot;335559740&quot;:259}\"><strong>\u00a0<\/strong><\/span><\/h5>\n<p><span data-contrast=\"auto\">Excel has a limit of 1,048,576 rows. However, the\u00a0number\u00a0of rows you can add to the memory of the Data Model is almost limitless.\u00a0Few Excel users are aware of a data model in Excel\u00a0called Power Pivot. Not only can it store billions of records, but the compression technology Microsoft has developed for this tool is outstanding. <\/span><\/p>\n<p><span data-contrast=\"auto\">If you are working with huge data sets, the combination of Power Query and Power Pivot is the ultimate winner. You can connect to huge data sets. Then you can clean, structure, merge and append your data in Power Query. Afterwards, you can transfer the data to the Excel data model and relate the data sets.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<h5><span data-contrast=\"auto\"><strong>Power Query \u2013 THe Pros and Cons<\/strong>\u00a0<\/span><\/h5>\n<p>To be honest, there are really only pros.<span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">Any Excel user above a basic level should explore this tool; it will change how you use Excel. For most users, it will also have a significant impact on the time spent in Excel.<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\"> Such is the magic of Power Query.<\/span><\/p>\n<p>Pros:<span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<ul>\n<li><span data-contrast=\"auto\">Positively impacts the efficiency of Excel tasks<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/li>\n<li><span data-contrast=\"auto\">Removes boring time-consuming repetitive tasks<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/li>\n<li><span data-contrast=\"auto\">Automates tasks without complicated VBA codes<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/li>\n<li><span data-contrast=\"auto\">Makes other tasks simpler because data quality and structure will be improved<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/li>\n<\/ul>\n<p>Cons:<span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">N\/A<\/span><span data-ccp-props=\"{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:259}\">\u00a0<\/span><\/p>\n<p>Further Reading:<\/p>\n<p>Want to learn more about Power Query?<\/p>\n<p>Have a look at some of our other free resources on the topic:<\/p>\n<p><a href=\"\/b\/solve-date-problems-in-excel-with-power-query\/\">Solve Data Problems in Excel with Power Query<\/a> &#8211; blog<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One word That describes Power Query in Excel \u2013 MAGIC!\u00a0 \u00a0Power Query will completely change the way you work in Excel!\u00a0For most Excel users, Power Query could be the tool that reduces time spent in Excel dramatically. Power Query could also minimise the use of VBA coding in Excel. It can be seen as a [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[637,4,8,9,18],"tags":[],"class_list":["post-5432","post","type-post","status-publish","format-standard","hentry","category-application","category-excel-training","category-microsoft","category-microsoft-office","category-technology"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/5432","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=5432"}],"version-history":[{"count":4,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/5432\/revisions"}],"predecessor-version":[{"id":7555,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/5432\/revisions\/7555"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=5432"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=5432"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=5432"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}