{"id":5675,"date":"2020-04-07T15:00:00","date_gmt":"2020-04-07T13:00:00","guid":{"rendered":"https:\/\/beebole.com\/blog\/?p=5675"},"modified":"2025-07-07T17:13:14","modified_gmt":"2025-07-07T15:13:14","slug":"excel-power-query-for-business-intelligence","status":"publish","type":"post","link":"https:\/\/beebole.com\/blog\/excel-power-query-for-business-intelligence","title":{"rendered":"Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal"},"content":{"rendered":"\n

What is Excel Power Query?<\/strong><\/h2>\n\n\n\n

Using Power Query<\/strong> in Microsoft Excel<\/strong> opens up the possibility of unlocking invaluable Business Intelligence<\/a><\/strong> for managers<\/strong>, which is exactly what we\u2019re going to cover today. But first thing\u2019s first: What is Power Query and where can you access it?<\/p>\n\n\n\n

\n
\n
\n

What is Power Query?<\/h2>\n <\/div>\n
\n

Excel Power Query allows users to connect to a myriad of databases, anything from a CSV file to SQL Server or Oracle, or even a table on a web page. The best part?<\/em> You do not need technical knowledge of how to connect to a data source, and the connections can be made using a few simple clicks right in Excel. You also don\u2019t need any knowledge of how to write a query on the data as this is all done using the Power Query Interface. In other words, this is an extremely powerful function.+<\/p>\n <\/div>\n <\/div>\n<\/div>\n\n\n

You can access Power Query on the\nData tab of the Excel ribbon.<\/p>\n\n\n\n

How can I use Power Query as a manager?<\/strong><\/h2>\n\n\n\n
    \n
  1. First of all, you can extract data from many other sources such as a web page, a database (like SQL Server or Oracle), or even just another Excel spreadsheet. <\/li>\n\n\n\n
  2. You can transform this data by joining sources together, removing columns that are not needed, and adding calculated columns, until you have the results in the exact format you want. <\/li>\n\n\n\n
  3. You can then present these results in the form of charts or as a pivot table in order to highlight important points for future discussion. <\/li>\n\n\n\n
  4. Report automation is another thing you can do with Power Query. Want to see it in action? Here’s a tutorial on how to build an automated time tracking dashboard in Excel<\/a>. <\/li>\n<\/ol>\n\n\n\n

    Before we get started, some prerequisites for these hacks.<\/strong><\/p>\n\n\n\n

    In some of the examples below, I\u2019ve\ncreated sample data in order to demonstrate the capabilities of Power\nQuery. It is assumed that these have already been loaded into Excel\nPower Query from a separate Excel spreadsheet.<\/p>\n\n\n\n

    Sales Data (1)<\/em><\/p>\n\n\n\n

    \"Business<\/figure>\n\n\n\n

    Sales Data (2)<\/em><\/p>\n\n\n\n

    \"Power<\/figure>\n\n\n\n

    Price Data<\/em><\/p>\n\n\n\n

    \"5<\/figure>\n\n\n\n

    Sales Detail<\/p>\n\n\n\n

    \"What<\/figure>\n\n\n\n

    5 Business intelligence hacks using Excel Power Query<\/strong><\/h2>\n\n\n\n

    1. Find unmatched items between two data sources<\/strong> <\/h4>\n\n\n\n

    An example of this is where there\nare two data sources, one being maintained locally, and one being\nmaintained centrally. The local one may simply be an Excel\nspreadsheet, and the central one may be a SQL Server database. These\nmay hold, for example, sales data, but the two data sources do not\nagree. Power Query allows the user to quickly find out the missing\nitems and resolve any differences, obviously saving one of your most\nvaluable resources\u2014time.<\/p>\n\n\n\n

    In the sample data, Sales Data (1)\nis different to Sales Data (2). Some items match, but there are some\nin each data source that do not match the other. In this case, the\nunmatched items are easy to see because the data sample is very\nsmall, but at work you\u2019d most likely be working with a much larger\ndata set. \n<\/p>\n\n\n\n

    If each source contains a huge\nnumber of rows, it would be a difficult task to find the individual\nitems in one source but not in the other. That is where Power Query\ncomes in. Using Power Query, you can find the unmatched items\nquickly.<\/p>\n\n\n\n

    Select Get Data<\/strong> on the far\nleft of the ribbon and then click on Combine Queries<\/strong>. Select\nMerge,<\/strong> and a pop up screen will appear to allow you to select\nthe two data sources to be compared.<\/p>\n\n\n\n

    Choose Sales Data (1)<\/strong> from\nthe drop down for the first table and Sales Data (2) <\/strong>for the\nsecond table.<\/p>\n\n\n\n

    Click on the Product<\/strong> column\nin each table to show that this will be used to join the two tables\ntogether. These will be highlighted.<\/p>\n\n\n\n

    The Join Kind<\/strong> drop down shows\na number of options. Choose Full Outer. <\/strong>Your screen should now\nlook like this:<\/p>\n\n\n\n

    \"Excel<\/figure>\n\n\n\n

    Click on OK <\/strong>and the query\nwill be created.<\/p>\n\n\n\n

    The query result will need expanding\u2014click on the arrow symbol<\/strong> in the right of the Sales Data (2) header to see this screen.<\/p>\n\n\n\n

    \"5<\/figure>\n\n\n\n

    Click on OK ,<\/strong>and this will\nshow the full detail of the query result. Where rows do not match you\ncan see null<\/strong> values are shown.<\/p>\n\n\n\n

    Click on OK,<\/strong> and then select\nClose & Load<\/strong> from the far left of the ribbon.<\/p>\n\n\n\n

    The data will be loaded into a new\nsheet, and the null values will be shown as blanks e.g cells A4, D6:<\/p>\n\n\n\n

    \"What<\/figure>\n\n\n\n

    This shows a comparison between two\ntables, Sales Data (1<\/strong>) and Sales Data (2)<\/strong>. They have\nidentical column names of Product and Sales.<\/p>\n\n\n\n

    You can see that tiles and\nplasterboard exist in Sales Data (2)<\/strong>, but not in Sales Data\n(1)<\/strong>. Conversely, Bricks exists in Sales Data (1)<\/strong> but not\nin Sales Data (2).<\/strong><\/p>\n\n\n\n

    You can now use the filters in the column header to isolate the blank values so as to show what is in one table and not in the other. Note that the query will automatically be called Merge1<\/strong>, but you can change the name.<\/p>\n\n\n\n

    2. Merge more than two data sources to give one result<\/strong> <\/h4>\n\n\n\n

    It is usual in a relational database for data to be held across several different tables. For example, one table may hold customer information, another table may hold orders that the customer has placed, and a further table may hold details of the actual products that were ordered. Or perhaps you’d like to combine your employee time tracking and timesheet data<\/svg><\/span><\/a> with information from other sources. Whatever your case, Power Query can help.<\/p>\n\n\n\n

    These tables are all linked by a unique ID, but to report anything meaningful from this, the tables have to be joined together to form one query. <\/p>\n\n\n\n

    On the unmatched query above, you\nmay want to add pricing data to it. The Merge screen only allows for\ntwo tables or queries, so how do you bring in pricing data?<\/p>\n\n\n\n

    The answer is to create another\nquery based on Merge1<\/strong> and Pricing Data.<\/strong><\/p>\n\n\n\n

    Select Get Data<\/strong> on the far\nleft of the ribbon and then click on Combine Queries<\/strong>. Select\nMerge,<\/strong> and a pop up screen will appear to allow you to select\nthe two data sources to be compared.<\/p>\n\n\n\n

    Select Merge1<\/strong> for the first\ntable and Price Data<\/strong> for the second.<\/p>\n\n\n\n

    Click on Sales Data (2).Product<\/strong>\nin Merge1<\/strong> and Product<\/strong> in Pricing Data<\/strong> to\nindicate the columns to be joined. Your screen will look like this:<\/p>\n\n\n\n

    \"5<\/figure>\n\n\n\n

    For Join Kind<\/strong> choose Full\nOuter.<\/strong><\/p>\n\n\n\n

    Click OK<\/strong> and the query result\nwill be shown.<\/p>\n\n\n\n

    Expand the Price Data<\/strong> columns\nby clicking on the arrow symbol in the header (check the previous tip\nif you\u2019re unsure here.)<\/p>\n\n\n\n

    Select Close & Load <\/strong>from\nthe far left of the ribbon, and a new sheet will be created with the\nresults of three different data sources:<\/p>\n\n\n\n

    \"Excel<\/figure>\n\n\n\n

    Note that Bricks is shown in Price\ndata as orphaned because we joined on Sales Data (2).Product<\/strong>\nand Bricks was missing from Sales Data (2).<\/strong><\/p>\n\n\n\n

    The new query has now been automatically called Merge2, but the name can be changed<\/p>\n\n\n\n

    3. Creating a calculated column<\/strong> <\/h4>\n\n\n\n

    In the final output from Power\nQuery, you may want to perform a calculation based on specific fields\nwithin the final output. For example, you could have the number of\nproducts sold, and the price for each product. However, there is\nnothing showing the overall value of what has been sold, which is\nuseful in reporting to senior management. This is where a calculated\ncolumn<\/strong> can be used.<\/p>\n\n\n\n

    In the result above, you may wish to\nadd a column that shows the value of the sales in Sales Data (2).<\/strong><\/p>\n\n\n\n

    In the Queries and Connections<\/strong>\npane (on the right of the screen), right click on Merge2<\/strong> and\nselect Edit.<\/strong><\/p>\n\n\n\n

    \"5<\/figure>\n\n\n\n

    A new window will appear showing\nyour query and a new ribbon.<\/p>\n\n\n\n

    Selectthe Add Column\n<\/strong>ribbon tab, and then click on Custom Column<\/strong> in the General<\/strong>\nsection of the ribbon.<\/p>\n\n\n\n

    A screen will appear for you to\ndescribe your calculation. The default name for the column is Custom,\nbut you can provide a new name.<\/p>\n\n\n\n

    Enter the formula into the Custom\nColumn Formula box:<\/p>\n\n\n\n

    [#”Sales Data\n(2).Sales”]*[Price Data.Price]<\/p>\n\n\n\n

    You can do this by double clicking\non the Available Columns<\/strong> list.<\/p>\n\n\n\n

    Click OK<\/strong> and, the new query\nwill be shown with the additional calculated column<\/p>\n\n\n\n

    Select the Home tab<\/strong> and click\non Close and Load. <\/strong>Your\nedited query will now be displayed on the original sheet:<\/p>\n\n\n\n

    \"Business<\/figure>\n\n\n\n

    4. Use Group By to summate a query<\/strong> <\/h4>\n\n\n\n

    Power Query will by default produce\na unique row for each row of the query, though this may be too\ndetailed for reporting purposes. If\nyou want to see how many of a particular product has been sold, then\na simple query showing every instance of the product would not be\nvery helpful. You can use Group By<\/strong> to summate by product to\ngive the total sold for each product.<\/p>\n\n\n\n

    On the sample data for Sales\nDetail<\/strong>, the products cover multiple rows so that there may be\nseveral values for a particular product.<\/p>\n\n\n\n

    You may wish to see the total values\nby product in your query. \n<\/p>\n\n\n\n

    To do this, right click on the\nProduct Details <\/strong>table in the Queries and Connections<\/strong> pane\non the right-hand side of the screen. Select Edit <\/strong>and the\nEditing screen will appear.<\/p>\n\n\n\n

    Click on the Product <\/strong>column\nheader if this is not already highlighted\u2014this is the column you\nare going to group by.<\/p>\n\n\n\n

    Select Group By<\/strong> on the ribbon\n(under Transform<\/strong>), and a Group By<\/strong> window will appear.<\/p>\n\n\n\n

    Group By<\/strong> will show as\nproduct. Enter \u2018Total\u2019 in the new column name box, and change the\nOperation<\/strong> to Sum<\/strong>. Change the Column<\/strong> name to\nSales<\/strong> (this is the column to be summated).<\/p>\n\n\n\n

    \"A<\/figure>\n\n\n\n

    Click OK<\/strong>,\nand your summated query will be shown. Select Close & Load<\/strong>\nfrom the ribbon, and the query will appear on a new sheet, showing\ntotals for each product.<\/p>\n\n\n\n

    \"Excel<\/figure>\n\n\n\n

    5. Fast Data Load<\/strong> <\/h4>\n\n\n\n

    When importing data from other\nsources such as databases this can be slow, particularly if there are\na large number of records. To speed this up, right click on the data\nsource showing in the Queries and Connections<\/strong> pane on the\nright-hand side of the screen.<\/p>\n\n\n\n

    Select Properties<\/strong> and a\nproperties window will appear.<\/p>\n\n\n\n

    \"What<\/figure>\n\n\n\n

    Tick the box for Enable Fast Data\nLoad.<\/strong><\/p>\n\n\n\n

    Excel Power Query for smart business management<\/strong><\/h2>\n\n\n\n

    I hope these business hacks using Power Query in Excel have proven helpful\u2014when used correctly, it can save you time and resources, as well as help unlock powerful insights that lead to better reporting<\/a> and decision making in management. If you have any questions or comments, let us know with a comment below. <\/p>\n\n\n\n

    As always, if you\u2019ve got an idea for a new tutorial<\/a> (we’ve already written about automating cell phone data usage analysis<\/a>, annual compensation reviews<\/a> and more), we\u2019d love to hear from you as well!<\/p>\n\n\n\n

    \n\t\n\t\t\t\t\t
    <\/div>\n\t\t\t\t
    <\/div>\n\n\t\t
    \n\t\t\t
    \n\t\t\t\t\n\t\t\t<\/div>\n\t\t\t
    \n\t\t\t\t\t\t\t\t\t
    RELATED POST<\/div><\/div>\n\t\t\t\t\t\t\t\t
    How to build an automated report in Excel with Power Query: The time tracking dashboard<\/div>\n\t\t\t\t\t\t\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t\t\t\tRead more\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t<\/svg>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t<\/a>\n<\/div>\n\n\n

    \u2014
    Photo by @jonathan_francisca on Unsplash<\/p>\n

    The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company\/tool or their past\/present organizations, employers, or associates.<\/div>","protected":false},"excerpt":{"rendered":"

    What is Excel Power Query? Using Power Query in Microsoft Excel opens up the possibility of unlocking invaluable Business Intelligence for managers, which is exactly what we\u2019re going to cover today. But first thing\u2019s first: What is Power Query and where can you access it? You can access Power Query on the Data tab of […]<\/p>\n","protected":false},"author":19,"featured_media":10695,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[123],"tags":[3989,4012],"class_list":["post-5675","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-productivity-time-management","tag-excel","tag-tutorials"],"acf":[],"_links":{"self":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5675","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/comments?post=5675"}],"version-history":[{"count":12,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5675\/revisions"}],"predecessor-version":[{"id":14003,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5675\/revisions\/14003"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media\/10695"}],"wp:attachment":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media?parent=5675"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/categories?post=5675"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/tags?post=5675"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}