• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query - Drag and Drop

T

TonyNZ

Member
Hi
I have a dataset of 170 Columns and about 7000 Rows.
In the Transform, I need to drag many columns appearing at different locations in the dataset, to the front. I can Select any column and drag it to the front. However, it become very time-consuming when dragging individual column each time. eg Column numbers 8, 16, 20, 30, 31 125, 130, 160 in the dataset, when transforming, I want this columns to appear in the sequence of 1st column (original 130), 2nd column (original column 20), 3rd column (original column 31), 4th column (original column 16), 5th column (original column 160), 6th column (original column 30, 7th column (original column 125 & 8th column (original column 8). Currently, I have to manually drag these columns, and keep dragging till I reach the desired column position. Is there a quick way to drag and drop and move columns to the desired position? Please help.
Regards
 
Right click on the column and scroll down to Move. Then select Move to beginning. Do these in the reverse order of the locations.
 
Hi Alan, yes, I can see that this will work.
I knew that we can select and Move to the beginning but the thought never came to mind how this could be exploited to get the desired result.
Thanks
Regards
 
Hi Alan, while creating a 2nd query, I realized that 16 headings that I wanted to use are in Column numbers 94 to 109. There were few other random headings that also I had to use. So, I selected column 94 to 109 and then used Ctrl key to select few others and I used 'Grouping' and that appears to be a good solution too. Thanks again; your original idea inspired me to explore further.
Regards
 
Tony, I'm new to power query so this might not be the most efficient method, but I thought maybe it could help! :)

I have about 8,000 rows and 215 columns and was able to prepare a single command in Power Query to reorder (full command shown below).
Just change to your data source's name and list the columns by number in the order you want them to appear. Ahead of time, in a regular excel spreadsheet, I transposed the headers with column numbers in the adjacent column, then ordered as I wanted them to appear, then used & to add "Column" to the #, insert commas in between, etc, to generate the completed command.

I used this command in the sample file prior to promoting first row to headers, and then used a similar approach to rename my columns as well. I have a feeling this is the long way around but I'll take anything that works while I'm learning.


= Table.ReorderColumns(general_report_Sheet, {"Column174", "Column50", "Column49", "Column1", "Column172", "Column175", "Column184", "Column185", "Column6", "Column2", "Column10", "Column9", "Column140", "Column135", "Column111", "Column12", "Column13", "Column68", "Column69", "Column70", "Column202", "Column186", "Column187", "Column62", "Column63", "Column64", "Column138", "Column139", "Column182","Column95", "Column203", "Column101", "Column183", "Column143", "Column87", "Column97", "Column121", "Column122", "Column123", "Column124", "Column161", "Column160", "Column88", "Column162", "Column211", "Column212", "Column213", "Column214", "Column7", "Column8", "Column168", "Column166", "Column167", "Column51", "Column52", "Column53", "Column54","Column55", "Column60", "Column58", "Column57", "Column56", "Column59", "Column112", "Column44", "Column163", "Column113", "Column126", "Column127", "Column144", "Column156", "Column164", "Column66", "Column188", "Column198", "Column5", "Column4", "Column98", "Column114", "Column11", "Column102", "Column103", "Column104", "Column105", "Column106","Column107", "Column67", "Column194", "Column125", "Column197", "Column199", "Column15", "Column96", "Column14", "Column181", "Column158", "Column119", "Column137", "Column165", "Column157", "Column134", "Column61", "Column178", "Column116", "Column159", "Column145", "Column100", "Column189", "Column115", "Column117", "Column136", "Column180", "Column179", "Column118", "Column210", "Column142", "Column99", "Column86", "Column85", "Column171", "Column141", "Column72", "Column71","Column177", "Column65", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column3", "Column146", "Column37", "Column38", "Column150", "Column36", "Column39", "Column33", "Column34", "Column151", "Column32", "Column35", "Column29", "Column30", "Column152", "Column28","Column31", "Column25", "Column26", "Column153", "Column24", "Column27", "Column190", "Column94", "Column191", "Column193", "Column21", "Column22", "Column147", "Column20", "Column23", "Column17", "Column18", "Column155", "Column16", "Column19", "Column41", "Column42", "Column154", "Column40", "Column43", "Column208", "Column207", "Column206", "Column209", "Column205","Column46", "Column45", "Column48", "Column47", "Column200", "Column89", "Column148", "Column201", "Column108", "Column90", "Column110", "Column109", "Column128", "Column91", "Column130", "Column129", "Column131", "Column92", "Column133", "Column195", "Column93", "Column149", "Column196"})
 
You must log in or register to reply here.
Back
Top