PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

(adsbygoogle = window.adsbygoogle || []).push({});

Contents

HOW TO SPLIT COLUMN IN POWER QUERY

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

EXAMPLE DATA

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 .
how to split column in power query
CHOOSING SPLIT COLUMN 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.
how to split column in power query
CHOOSE SPACE AS DELIMITER

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 ]

how to split column in power query
RESULT - TEXT SPLIT INTO MULTIPLE COLUMNS IN POWER QUERY
(adsbygoogle = window.adsbygoogle || []).push({});

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

  • PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
  • KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE  AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON [email protected]
  • Didn’t get what you were looking for?? Just put your question in the chat box present in the Right Lower corner. Name and email id [No spamming, just for the answer of your requirement] are needed.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: