The digital world has generated huge amounts of data in every industry. Data handling is intricate as it contains and conveys information that helps build intelligent systems or frameworks. Planning, forecasting, and decision-making are done based on past data.
Entities such as processes, clients, employees, and finance all contain data. The real challenge is that they are from various sources in different formats, and some need a separate tool to handle them.
Microsoft Excel is a versatile tool that handles data storage, manipulation, and processing. They make the job of sorting, report generation, and comparison much faster. Whatever your profession or technical expertise, taking courses in Excel and Microsoft Office is beneficial.
What Is Power Query?
Power Query is a business intelligence tool used to manipulate data for analysis and business intelligence. Data analysts spend time analyzing data to manipulate, combine, perform lookups, and automate. Power Query gives a graphical interface to automate simple and repeatable actions.
It is a data preparation engine that transforms data. Also, it has a graphical user interface for getting data from different sources and a Power Query editor for applying transformations. Power Query helps you complete the Extract-Transform-Load (ETL) processing of data.
Power Query does repetitive manual work, such as copying and pasting, combining columns, or applying filters. This tool is easier to use than other BI tools, such as Tableau, Qlik Sense, and Oracle BI.
How to Enable Power Query?
Power Query comes built-in with Microsoft Excel 2016 (Office 365). It is found in the Data tab of the Excel ribbon in the Get & Transform Data group. If you are using Excel 2016, select Data → Get Data → Launch Power Query Editor to open the Power Query editor to create a query.
If you are using Excel 2010, follow these steps to install the bit version (32 or 64-bit).
- Click on the File button.
- Click the Help option on the left side.
- Click the Excel version that is listed under the Product Activated section.
- The bit version (32-bit or 64-bit) will be listed under the About Microsoft Excel section.
If you are using Excel 2013, follow these steps to install the bit version (32 or 64-bit).
- Click on the File button.
- Click the Account option on the left side of the menu.
- Click the Excel version listed under the Subscription Product section.
- Select the bit version listed in the first row of the pop-up window.
What Can Power Query Do?
Excel Power Query can filter and combine data from one or more supported sources and mashes it up. Such data mash-up is expressed using the Power Query Formula Language, informally known as M. It works with Analysis Services, which is an Analytical Data Engine used in decision support systems and business analytics, Excel, and Power BI workbooks.
Power Query provides a platform for data connectivity and data preparation that helps end users import and transform data from Excel and Microsoft products, Power BI, and Dataverse.
Power Query is a business intelligence tool that allows you to import data from different sources and clean, transform, then reshape your data as needed. So, you can set up a query and reuse it with a refresh without rewriting it.
Import Data with Power Query
You can import and clean millions of rows and columns into the data model for further analysis. This is easy when compared to Excel formulas or VBA. The Power Query editor records all the transformations step by step and converts them to M code that is similar to a Macro recorder with VBA.
Power Query M language is a functional, case-sensitive language that is used to filter and combine data from one or more of a rich collection of supported data sources.
Import Data,
You can import data from the following files into a format or view you want.
- File – Excel Workbook, Text/CSV, XML.JSON, PDF, or from a folder
- Database – SQL Server Database, Microsoft Access Database, Analysis Services, and SQL Server Analysis Services Database
- Azure – Azure Data Lake Storage and Azure Data Explorer
- Power Platform – Dataflows and Dataverse
- Other Sources – Table/Range, Web, Microsoft Query, OData Feed, ODBC, OLEDB, Picture, and Blank Query
A text file is chosen and converted to a table, like in the example above. Select Data → Get Data → From File. Choose a text file, and click ‘Import.’ The text file then appears in a table. At the bottom of the window, you will have the options Load and Transform.
When you choose the option Load, the text file is loaded as a table. If you choose the option Load to, you will get the window prompting you to select how you want to view the text file in the workbook.
Transform Data
Now that you have imported the table in the view you want, you can transform the data according to your needs. Select the Transform option next to the Load, and the Power Query editor opens with the table in it.
The above example shows that a .txt file is imported using the Power Query feature. A well-formatted table is created with the fields in the file, and we can import this table into a workbook as a table, create a pivot table, or create a chart.
But, if you wish to perform more actions, select the Transform option. This opens the Power Query Editor with more options in the ribbon, as seen below.
The Home tab contains an Advanced Editor, where you can see the script for importing the .txt file into Excel. Click on that option to look at the script. You need not write anything, but this can be used if you wish to code it yourself.
There are a lot of features that you can apply to the table that you have imported. In the example above, you can see that the columns to be displayed are chosen. You can choose the rows you want to display, sort, transform, combine, and edit, then set permissions for the data source and get an Excel query from other resources.
Output Data
After applying the necessary changes to the table and transforming it, you can load the transformed table back into your workbook. The Power Query editor has the option Close & Load on its ribbon. Select it, and you can find the table where you applied the changes loaded on the workbook.
Using the Power Query in Excel
Using Excel Power Query, you can import or connect to external data and then transform that data to meet your needs. This data can be transformed into tables with headers, and columns can be removed. The data type can be changed, merge tables, and create charts and reports. The following are four phases in using Power Query:
- Load/Connect – Make connections to data in the cloud, to a different service platform, and with local systems.
- Transform – Retain the original data while you transform data to produce the required view.
- Combine – Combine data from multiple sources.
- Load – Load the transformed data into a worksheet and complete the Excel query.
You can use Power Query to import data from a single data source, such as an Excel workbook, multiple databases, the web, Microsoft Azure, feeds, or from cloud services. When you have integrated data from different sources, you can reshape the data to uncover the insights held in the data.
When the original data is updated, the changes can appear in the transformed data by refreshing it. Additional columns, modifications, and deletions in the external data source are also visible in the imported data.
The above screenshot shows the query built automatically for the series of actions that were done on the data in the table. The steps are as follows:
- Data is loaded into the Power Query dashboard.
- Required columns are chosen.
- It is sorted descendingly.
- The filter option is used to choose only those rows that satisfy a condition.
In the Advanced Editor, you can see the queries built automatically for the actions you performed on the table. Power Query is a query-building tool that converts the above steps into queries and presents only the data required for analysis and processing or can be saved as such.
FAQs
1. What is the difference between Power Query and Excel?
Power Query can import data from different databases, while Excel is limited to data saved in the local system. Power Query has an interactive and customizable dashboard that can be easily accessible on mobile devices and has faster processing.
2. What is the purpose of using power query?
Power Query is used to extract data from different sources and transform them in a way that can be used. Then, the transformed data is loaded back into the database and saved for further analysis.
Closing Thoughts
Power Query is a business intelligence tool that imports data from various sources, transforms and reshapes it, and presents it as a table, chart, or report. It builds a query that can be reused with a simple refresh every time there is a change in the original table.
The transformed data contains meaningful interpretations for further analysis, decision-making, and planning. Data analysts and business analysts analyze data to identify, analyze, and interpret trends to improve their business strategies.
You can enroll for Microsoft Excel course level 1 to learn the basics of Excel. If you are looking to work as a data analyst or business analyst, gaining a certification in Microsoft Excel course level 2 will give you a competitive advantage over other applicants.
