After 7 years of development, Power Query really gives users many outstanding features. However, up to now, many Excel users in Vietnam still have not figured out what Power Query is. In the past, people who were supposed to be Excel experts would use VBA tools to automate data, as simple as copying and pasting data from multiple sources. But not everyone has the ability to code – this VBA programming language. So the solution of modern Excel is to use Power Query.
Viewing: What is Query
What is Power Query?
In a nutshell, Power Query is a collection of features, dedicated to the purpose of fully automated data processing. Aimed at users who are not able to use the programming language. In fact, this is a feature that needs to be added-in (extension) from within Excel 2010 versions. However, from Excel 2016, Power Query has been shown in the Data section.
What is power query?
Power Query Applications in Excel
Power Query in data automation
Using this tool on Excel will help you automate data quickly. I can give an example for you to imagine as follows:
Example: Employee A has to download 10 data files from the system every day. This employee’s job is to copy these 10 files and put them in 10 different sheets of another Excel file, then write the formulas for the sales of the previous day. Work performed on a daily basis. If employee A does not know how to use the Power Query tool, you must do this operation for an average of 20 minutes per day. However, connecting to data and refreshing data using Power Query, this operation only runs less than 1 minute.
In addition to the example above, Power Query has many other applications in data automation. Please refer to our foundation course.
Application in data cleaning
When we receive data files from others, we do not always have a standard data to work with. Therefore, data normalization is a must and prerequisite before processing.
See also: Download Sad Monkey Game, Sad Monkey Game: Pirate
The following figure will describe to the reader how a data table is structured correctly and incorrectly
What is power query?
Therefore, usually when receiving unstructured data, it will take a long time to process. However, with the application of Power Query tool in data cleaning, it will save you a lot of time. For the above data case, you just need to use the Unpivot feature to get the table with the correct structure within 1 click. In particular, Power Query still retains the original original data, even though the user has changed the format and formula inside.
Use Power Query in data analysis
By manipulating and using the very simple M Query or drag-and-drop language, users can write Excel formulas or combine data from multiple sources. The loaded results will not include intermediate formulas, helping to reduce the size of the data file. At the same time present necessary information for the reader.
In addition, Power Query is an effective tool for data analysts – Business Intelligence in analyzing multi-source data. By combining with Power BI, it will help users simulate results through charts and tables automatically.
How to teach yourself Power Query
?With its user-friendly design, Power Query is a great tool for people who often have to work on Excel. Using this tool is very simple, with only data connection, transformation, or combining and finally sharing of data. Therefore, users can self-study with guidance from our basic Power Query course to be able to automate 80% of the daily tasks that you are manually manipulating every day, while also knowing add other outstanding features of Power Query. Besides, you can refer to the article sharing between learning Power Query and VBA, I believe the popularity and application of Power Query in work is much more widespread and simpler.
See also: What is Trending – What is Trend
Nguyen Minh Nhat, CMA
With the desire to share the most complete and up-to-date knowledge about data and analysis with everyone, I have spent all my time from the time I graduated from college until now to continuously do in-depth research, experience many things. positions that directly harness value from data at companies across a variety of sectors to deliver a COMPREHENSIVE ANALYSIS program that blends foundational theory and applied practice. I and the team at UNIACE hope that this program will inspire people about the profession, especially the next young generations, to balance the shortage of analytical human resources in Vietnam.