Transpose using power query | How to convert horizontal data vertically | pasting data vertically

hello friends we all know that if you want to convert horizontal data into vertical we use the function called transpose for example if we have this data for the schools and the items they purchase from jan to december and if you want to convert this to vertical data which is now currently in horizontal we can use the transpose function and with the help of that the data will get converted like this but if you look at this data the data is get got converted into vertical data but if you see the school name is appearing again on the top which confuses the reader but if you look at this particular data which is again converted into vertical data here we have school names as a different column and item names as a different column and the month column has been added opposite to that the values different values are being converted vertically this looks better than this data because this particular conversion is being done with the help of power query it is not very difficult to use the power query so we are going to see in today's video how to convert horizontal data into vertical with the help of power query so stay tuned till the end of this video if you are not subscribed to the channel yet please go ahead and subscribe to the channel [Music] hello all welcome to boost your career the place where you learn the skills to grow in your career now this is the data that we have these are the school names two schools we have these are the items they purchase this is the amount for from the month of january to december they spend on different items now this we want to convert into vertical data with the help of power query so first of all we'll go to the data now this is a simple data this is not a table format if you want to convert into table format you can just use ctrl t and do that but right now i will show you how to convert this data using power query and power query itself will convert this data into table so we go to data and then we'll import from the table you see here from the table i i have i had my active cell into the cursor it's into the table itself here that is the reason it got automatically selected if i put my cursor outside then i need to select for the data from this and i'll say okay now this power query window opens this power query is there since uh in excel since 2016 version so after 2016 if you're using that you can use this now if you see this data is there and we have the data from january to december like that the way we have now first of all we'll select the two columns the first two columns and first two columns i selected by clicking on the top and then right click and then i will go to unpure other columns unpaired other columns that means it will unpure the other columns which are there now if you see this data got converted into horizontal uh vertical data which was horizontal originally now here if you see this attribute when another column of month has been added so i'll change this column name by double clicking on that and i will name this as month now if you see here for this particular school jkp which is the second school the stapler data has started from the month of february because in original data there was no data for the january it started with february only if you see here that is the reason that no date no data is appearing for the month of january but here i want the data to be appeared as a zero that means i want the blanks to be converted into zero so for that i will go to again the first query that is changed type and then uh i will go here and replace values replace values replace values for that i will select this data from the month of january you can click on the column side and shift right arrow once you select that then go to replace date replace values so i'm in the first query i am i've selected the data and go to replace values it will ask you are you sure you want to insert the step yes i want to insert the step to convert this into zeros inserting so that is the error that is the warning message that appears but still i want to go ahead and insert it value to find now what i want to find null and that should be converted into replace with 0 now whatever blanks are there this got converted into zero then i will go again here and then say close and load i want to load into new worksheets so i will click load you will see that this data got converted into vertical if i add any other step i'm adding another entry here sip pins and one blank i will click all other blanks i will click then again i will go here right click and refresh you'll see that the pins data is also being appeared and for all other months it shows zero it looks better than the transpose function it is very easy to use so this is the way we convert the horizontal data into vertical with help of power query i hope you like this video if you want to learn such skills please do subscribe to the channel thank you very much

Channel: Sandeep Jadhav

Views: 25,354

Keywords: Tranpose in excel, power query in excel, excel functions, how to paste data vertically, how to convert horizontal data vertically, Transpose, excel, excel tips, excel query, power query

Id: Jyl4UPnuJyI

Length: 6min 10sec (370 seconds)

Published: Sun Feb 13 2022