How to CONCATENATE BIG DATA TEXT, NUMBER, DATE, NULL Columns and Rows in Power Query Excel 2022!

In this blog I will describe you, how to concatenate big data text, number, date, null columns and rows in power query excel. If you follow me, you will be able to concatenate text column. number column, date column, null column, with delimiters. For the first example we are going to show how you can concatenate text columns. Why do we need to concatenate text columns! Imagine your boss is asking you to give him all the contact details or address of your customers from customers data right now!

CONCATENATE Columns and Rows in Power Query Excel

We are going to use a master data which has 685 rows and 17 columns out of these master data if you want to come with a concatenated address list, this thing can give you a bit headache. But if you use the power query, it will serve you within a minute. First we will have to go to our Data section. From data, we will have to click From Table/Range. After that you will see your power query editor will open. Here we will remove all the other columns, except the name problem postcode and country out of 17 columns. We will just only keep this four columns. We will remove the other columns. To do that,  we will have to do is just go to Remove Column and click Remove Other Columns. It will remove all the other columns. So now we will have to concatenate these four columns. If want a visual help you can check this below video. 👇


                         Subscribe My Channel: Techh Designn

Now we want to concatenate this address. First we want name, after that we want the province, then want postcode and after that we want country. So select all the columns in the same Exact Order and go to transform and click merge columns. A box will pop up, here it will say merge columns. From here you will have to select the separator. I will choose only comma and for the new column name, I am going to choose the address and just click ok. Just check this out, it has concatenated all the columns. Now remove all the duplicates, to have only the unique address list. To remove all the duplicates just go to remove rows and click remove duplicates. After that just click close and load and close and load too. We will select a new worksheet and click ok. Check this out, it has given us a full answer. The required data are here. Now you can print it or email this information to your boss. 

In the second example we are going to show how you can concatenate numbers. So as usual first, we will have to go to data from data we will have to go to from table/range. It will open the power query editor. Here we want only the materials and the province. We are going to move it to the 1st column. And will bring materials to the front. We also want the net value also we will bring it to the front. Now most important part select all the required columns. Just go to remove column and remove other columns. We have just only our required columns on our editor. So now select the province column and the material description go to transformation click group by. A pop-up will come, just change the column name to cells and operation make it just sum and column net value and hit ok. If you see our data has changed. We will have to change this sales column to currency.

Now we will see our data has changed. Select the material description and cells and merge the columns. I'm selecting the equal sign separator and changing the column name to the sales total. Again we will select the province column and go to group by a pop-up will come here, we will change the column name again sales total. Change the operation to sum and select the column sales total and hit ok. Error will appear, no problem we will write a little code Text.Combine([Sales Total],",") here, it will solve the problem. Now if you see we have a summarized data where we have three province and as well as the sales total where the materials are given as per the material's sales amount. Just copy this and send it to your boss or you can print it.

For the third example we are going to work on date column then just select the date column and go to add columns. From here just click date and after that if you want the year to come in a separate column. Then again select the date column and go to date and select the month, for me I want the month name click it and after that you will see a separate column. With the months here I want to visualize this month differently, so just select the month column and go to extract. Here click first characters and the amount I'm giving it 3 and hit ok. You will see all the first letters are here for the months. So I don't need the another month column so I'm going to delete that column. Bring the month column before the year column. Select the month and year column and just do merge columns. For me I'm just selecting the space delimiter and hit ok. We have a column with the month and the year. Bring it to the front and we don't need another date column, so we are going to delete this column. Now select the month column and the material description column go to transform and group by. Here change the column name to cells. And operation will be sum and column will be net value and hit ok. Now change the sales column type to currency. And again select the material description and cells, do a merge. I'm selecting the equal sign delimiter I'm not changing the name and hit ok. Now again select the month column and select the group by. New column name, I'm not changing. It's operation will be sum and column will be merged and hit ok. You will see a change and it will show error. But no problem we are going to write a little code here. Here we will just write Text.Combine([Merged],","). Boom! See our data has changed. And summarized data is in our editor. So now you just copy and send it to your boss to show him the monthly summarized data.

For the fourth example, I'm going to show how you can concatenate null columns. If you see we have a data, here we have a null cell. Because one of my customer didn't give the LC, that's why I didn't write it to the cell. So here I'm going to work with this null value. We will select all the required columns and just like previously we will remove the other columns. We can do to combine the null value, we just have to go to add column and here you will see the merge column select merge columns. From here I'm going to use the comma delimiter. And I'm not changing the column name just hit ok. See, we have a separate merged concatenated column. So, this is the way you can actually concatenate the null values too. 

I hope you get the point, how to concatenate big data text, number, date, null columns and rows in power query excel. So if you like the blog, let me know in the comment section. And if you know another ways to do this sort of things let me also know in the comments section. Thanks for today, I hope you guys will be fine, have a great day.

Post a Comment

If you have any doubts, please let me know.

Previous Post Next Post