PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
If you have been using the MICROSOFT EXCEL software for some time, you must have heard or read about the word POWER QUERY.
POWER QUERY IS A DATA SHAPING TOOL WHERE WE CAN BRING THE DATA THROUGH A CONNECTION FROM VARIOUS SOURCES, DO SOME SHAPING SUCH AS REMOVING ROWS, COLUMNS, CHANGING DATA TYPES, TRIMMING THE DATA ETC. AND GET IT FINALLY FOR THE ANALYSIS IN OUR MAIN SOFTWARE SUCH AS MICROSOFT EXCEL OR MICROSOFT POWER BI .
This is an amazing tool in fact which has additional tools which are not available in the STANDARD EXCEL [ Although it is a part of the EXCEL only ]. Once the user starts using it, only then he would be able to feel the ease by which it handles the data and does some of the magnificent jobs.
The main steps of using the POWER QUERY are:
POWER QUERY has a separate environment which opens up as a separate entity.
We have power query in EXCEL as well as in POWER BI. Let us find out the process of opening the POWER QUERY in both.
STEPS TO OPEN POWER QUERY IN EXCEL 2019.
Go to DATA TAB>GET DATA> LAUNCH POWER QUERY
FOR OTHER VERSIONS CLICK HERE.
Let us get acquainted with the working environment of POWER QUERY.
LET US UNDERSTAND THE VARIOUS AREAS OF THE POWER QUERY EDITOR.
LOOK AT THE PICTURES BELOW. The two pictures refer to the POWER QUERY available in the MICROSOFT EXCEL and POWER BI.
Both are almost same with a few changes in the placement of the options. The pictures below can be looked at for the difference.
1.RIBBON:
The upper portion of the editor contains the RIBBON with different tabs. The ribbon contains the ready to use functionalities of the POWER QUERY EDITOR. We’ll learn all the functionalities in the next articles.
2.DATA PREVIEW:
It is the area where our data is visible. We apply the operations on the data such as column removal, data type change etc. , all are previewed here before finalizing.
3.NAVIGATOR PANE / QUERIES LIST [ON THE LEFT PART OF THE EDITOR] :
It shows the list of all the queries.
ONE QUERY WOULD MEAN TO FETCH DATA FROM A SOURCE.
It’ll be shown in this list. Suppose we fetched data from the EXCEL in one query, then from the web, then from the csv etc. , so it means we’ll have three queries which will be listed in the NAVIGATOR PANE.
4.QUERY SETTINGS PANEL:
This panel is not visible in the pictures below. It comes when we start editing the query.
It records all the steps taken by us and gives us an easy access to revert back to any step directly.
For example, suppose we renamed a column and deleted other. Both of these steps will be recorded and shown in the QUERY SETTING PANEL.
Before we move further, let us take a simple example and shape it using POWER QUERY to understand the basic working of POWER QUERY.
Here is the list of steps.
POWER QUERY is really flexible when it comes to the choices from where we can get the data from.
We’ll cover all the main sources but for this example, as given in the problem, we have been provided with a link from where we need to get the RESULT TABLE.
The link as already mentioned is https://en.wikipedia.org/wiki/Cricket_World_Cup.
Let us get the data from this link.
NAVIGATOR SHOWS THE COMPLETE DATA FETCHED FROM THE SOURCE. IT LETS US CHOOSE THE RELEVANT DATA OF OUR USE AND DISCARDS THE REST.
NAVIGATOR SHOWS THE COMPLETE DATA FETCHED FROM THE SOURCE. IT LETS US CHOOSE THE RELEVANT DATA OF OUR USE AND DISCARDS THE REST.
Our aim is not only to remove the numbers but to remove everything after the country name.
A ready option is available for this task in the POWER QUERY.
Now , the last steps is to rename the COLUMN HEADERS and remove the row with the null values.
*The complete process is shown in the animated picture below.
We have completed all the objectives and our data is clean and ready for the analysis now.
We can click CLOSE & APPLY which is the first button in the first data tab of ribbon. query will be closed and the ready data will be transferred to the application whether it is EXCEL or POWER BI.
The following picture shows the clean data which is ready.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE