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

Contents

HOW TO CONCATENATE CUSTOM TEXT TO COLUMNS 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 with the help of the POWER QUERY.

IN THIS ARTICLE, WE ‘LL LEARN TO CONCATENATE COLUMN DATA INTO A NEW COLUMN

 

WHAT IS CONCATENATION

CONCATENATION or TO CONCATENATE simply means the process of sticking together.
We use this process with the use of function or operator in Excel to stick any two or more texts, numbers or numbers and texts.
For example
1 concatenated with 2 will result in 12.
Hello concatenated with Sir will result in HelloSir. [ We didn’t put any spaces in between ].

HOW TO CONCATENATE CUSTOM TEXT TO COLUMNS IN POWER QUERY

We will take an example to understand the process of concatenating the columns to create a new column.

Let us take a table for the example which we will load from the EXCEL and transform it so that its loaded into POWER QUERY EDITOR.

CLICK HERE TO LEARN HOW TO GET DATA FROM EXCEL INTO POWER BI

 

The table for the example is shown in the picture below.

Read the article mentioned above.

In the navigator, use TRANSFORM DATA and the table would open in the POWER QUERY EDITOR.

 

Add Your Heading Text Here

FOLLOW THE STEPS TO CLEAN AND SHAPE THE DATA:
Before we try to conver the columns into rows, we need to do the following operations.
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

TABLE EXAMPLES
TABLE DATA IN POWER QUERY EDITOR

FIX THE HEADER OF THE TABLE:

We can see that the header is not the one we need.

So follow the steps to change the header and make the first row as header [Which is our header in actual].

  •  Click on the left upper corner of the table.
  • Choose the option USE FIRST ROW AS HEADERS.
  • It’ll directly replace the COLUMN HEADERS WITH THE FIRST ROW.
THE COMPLETE PROCESS IS SHOWN IN THE PICTURE BELOW.
CHANGE THE FIRST ROW INTO HEADER

Now, our data is ready for the main operation.

CONVERTING COLUMN TO ROWS IN POWER QUERY

STEPS TO TRANSPOSE A TABLE IN POWER QUERY EDITOR:

  • After the table is cleaned and shaped up, go to TRANSFORM TAB.
  • Choose TRANSPOSE. [TRANSPOSE IS THE WORD WHICH MEANS THE CONVERSION OF ROWS TO COLUMNS OR COLUMNS TO ROWS]
  • The table will be converted and the columns will become rows and rows will become columns.
HOW TO CONVERT ROW TO COLUMNS
STEPS TO CONVERT COLUMNS TO ROWS IN POWER QUERY

So, we have successfully converted the columns into rows and rows into columns. [Rows to column or column to rows, both means the same].

You must have notices that only the rows were transposed and not the headings. So how to get the headings also be transposed during the process.

There are a few hacks for that.

1. We already had our headings as a top row which we changed into the HEADERS. If we had kept it the same, the first row would have become the row headers.

2.Transpose the data if we need to have ROW HEADERS instead of COLUMN HEADERS. Now close and apply the power query editing and add the row headers in the main application.

 

HOW TO TRANSPOSE IN POWER QUERY
STEPS TO TRANSPOSE USING 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]