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 .
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 ]
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 ]
The following picture shows the data as it enters the POWER QUERY EDITOR
STEPS TO SPLIT A COLUMN INTO MULTIPLE COLUMNS IN POWER QUERY
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 ]
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE