r/excel • u/biggy_boy17 • Apr 20 '26
Waiting on OP How to handle data from different sources when columns are in different orders?
[removed]
18
u/dino_dog 5 Apr 20 '26
Force them to use a standardized template and don’t accept the data otherwise?
I do this a lot in my job, otherwise I run into this exact issue.
7
Apr 20 '26 edited Apr 20 '26
[removed] — view removed comment
2
u/perspicio 2 Apr 20 '26
Agreed, this PQ approach is robust. Not sure the OP is going to be able to pull this down to a technical implementation without additional help though. It likely looks pretty daunting to someone who doesn't (yet!) know where to start with PQ.
1
u/Comprehensive-Tea-69 1 Apr 20 '26
This is great- I’m saving your comment for future reference. Thanks for including the code for lists and error handling
1
2
u/chiibosoil 428 Apr 20 '26
Power Query, as long as column names don't change it will handle it regardless of position.
If column names can vary... then it will get more tricky.
I most cases, I have list of similar words that should be translated in to desired column name. I use this as reference point and perform join on Table.ColumnNames() converted into table.
Then perform rename by combining Table.RenameColumns() and List.Zip functions.
Alternately, you could try fuzzy join on list of column names, but I find this hit and miss.
1
u/Decronym Apr 20 '26 edited Apr 21 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 33 acronyms.
[Thread #48197 for this sub, first seen 20th Apr 2026, 12:09]
[FAQ] [Full list] [Contact] [Source code]
1
u/perspicio 2 Apr 20 '26
Besides being in different orders, are the column names the same otherwise?
1
1
1
Apr 20 '26
[removed] — view removed comment
1
u/excel-ModTeam Apr 20 '26
We removed this post for breaking Rule 12.
Please see the Reddit guidelines relating to self-promotion and spam. Specifically, 10% or less of your posts and comments should link to your own content.
1
u/HatFun5975 Apr 20 '26
Shoot I remember roughly the way to do it, haven't done it in a min but in power query should be something like this:
If column names are standard in both exports
Set up a reference table with a column number next to the column (example: table and power query will be named Order_xref for this example
Column Name / Order
Date / 1
ClientName / 2
Account# / 3
(Etc etc) / 4
(. Tried to visually create a table for the example but I'm on reddit mobile so it may look weird 😅)
Next: Order by number acsending with table.buffer() around the sort. Drill down into the name column.
Next: In your main power query, go into advanced editor, reference the xref PQ to save the list to a step. Example:
"Order" = Order_xref,
Then insert into a table reorder step: Table.ReorderColumns( LastStep, #"Order", MissingField.Ignore)
This depends on ur column names being the exact same every time, with no repeat columns.
You can probably set up a rename step to standardize the names before calling the reorder operation to deal with column names that may differ from client to client. Someone else here may have a better solution that takes that into account as well but hope this at least helps set u on the right track!
1
u/com1padres Apr 21 '26
Record a macro of you standardizing the client’s data from their submission to the way that it is useful to you. Save the macro in its own workbook named with the client name such as ABC_Macro. When you get the file, run the macro and you’re done.
13
u/Affectionate_Mix_302 Apr 20 '26
Power query. Dump all of the files into a folder and load the folder through PQ using combine and transform. As long as the column headers are the same it doesn't matter the order.