INTRODUCTION
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 .
FOR THE INTRODUCTION OF POWER QUERY, CLICK HERE.
Power Query tool, in fact, has immense powers to shape up the data in the way we want.
We’ll cover a lot of practical problems to sort them out with the help of the POWER QUERY.
In this article , we would learn to SPLIT THE DATA OF A COLUMN INTO MULTIPLE COLUMNS on the basis of any delimiter such as a comma, full stop, space etc.
This functionality is very useful in day to day reports making.
We have such function in EXCEL too which we know as TEXT TO COLUMN [ click here to learn text to column ]
GETTING THE EXAMPLE READY
For the example, we are going to take an Excel Sheet containing fictitious table just to understand the steps to split a column into multiple columns.
WE WILL SEPARATE THE COLUMN 1 OF THE TABLE AND SPLIT IT INTO TWO COLUMNS CONTAINING THE TEXT AND THE FIGURE SEPARATELY.
We have already performed a few steps which can be learnt here. [ www.gyankosh.net//powerbi ]
FOLLOW THE STEPS TO CLEAN AND SHAPE THE DATA:IMPORT THE DATA INTO POWER QUERY.
- Launch the POWER QUERY EDITOR.
- Choose , import from EXCEL.
- Select the EXCEL file.
- Choose the table from the navigator and click TRANSFORM DATA.
The following picture shows the data as it enters the POWER QUERY EDITOR
SPLITTING THE COLUMN IN POWER QUERY
STEPS TO SPLIT A COLUMN INTO MULTIPLE COLUMNS IN POWER QUERY
- After the table is cleaned and shaped up.
- Select the column which you want to split.
- Go to TRANSFORM TAB and click SPLIT COLUMNS and choose DELIMITER option .
- As our column contains a space, we choose the BY DELIMITER OPTION.
- After clicking BY DELIMITER option, the following dialog box appears [ as shown in the picture below ].
- Choose SPACE from the SELECT OR ENTER DELIMITER dropdown. [ In this case SPACE came by default. If it doesn’t appear, choose it manually ].
- Choose SPLIT AT radio button as per requirement. We want it to split at every occurrence, so we chose EACH OCCURRENCE OF THE DELIMITER. The options of LEFT-MOST DELIMITER can be chosen if we want to split the data on the basis of first occurrence of the delimiter from the left only or RIGHT-MOST DELIMITER can be chosen when we want to split the data on the last occurrence of the delimiter.
- CLICK OK.
- The first column will be split into two column containing the DATA BEFORE THE SPACE AND SECOND COLUMN WITH DATA AFTER THE SPACE.
After clicking OK, we can see that the column 1 has been split into COLUMN 1.1 and COLUMN 1.2.
We can change the column names easily by double clicking the header and changing the name. [ HOW TO RENAME THE COLUMN ]
