Reorder column headers for apend

[dilumd]

Hi All,

I have few files which need to be appended,

However, my problem is that columns are keep on chnaging every month (e.g. order of the column, new columns can be added or deleted). so inorder to append i need to adjust the all columns in order and take additional columns towards the end of the appended new table. (however i have about 50 to 60 columns each month and i need to append 6 month's data) 

Is there a easy way of doing this???

I tried reorder columns but i'm getting an error since some columns are missing in some months and some new columns are added

Month 1	EPF No	Basic Salary	Attendance Incentive	Production Incentive	Gross Salary	Taxes	Walfare	Net salary
Month 2	EPF No	Basic Salary	Attendance Incentive	Production Incentive	Allowance_1	Gross Salary	Taxes	Walfare	Net salary
Month 3	EPF No	Basic Salary	Attendance Incentive	Gross Salary	Taxes	Walfare	Net salary
Month 4	EPF No	Basic Salary	Attendance Incentive	Production Incentive	Allowance_1	Allowance_2	Gross Salary	Taxes	Walfare	Net salary

 Thank you in advance.

[v-chuncz-msft]

@dilumd,

I agree with the comment above. You may select Unpivot Columns in the Query Editor and use Matrix visual later.

[Greg_Deckler]

Well, if we are talking 6 files here, the easiest thing might be to just go into the source and fix them all manually to be the same format. Brute force, but effective. Otherwise, you might be able to do something with Transpose of pivoting, but if you are missing columns in some files that's going to be really tough because you'd have to add those columns effectively but how do you know to add a column that may or may not be added at some future point in time.

In the cases where I have run into similar issues, I have tended to format my source data like:

My Metric 1, ##

My Metric 2, ##

My Metric 3, ##

I can then Transpose this to columns. In this way, if columns are added to the end it generally isn't a problem in a combine binaries query.

Will think about it some more.

[dilumd · Accepted solution]

Hi All,

Thank you for your respond, however my data files have more than 100 columns every month and it’s changing so it’s a nightmare to combine manually. 

I tried below R script on power BI and got it sorted without any problem.

Please refer the below code if you’ll also come across similar issue in future.

Thanks again for the respond.

setwd("C:/Users/..../ Files")
library(xlsx)

Dec=read.xlsx("Dec 16.xlsx",1,startRow=5,header=TRUE)
Jan=read.xlsx("Jan 17.xlsx",1,startRow=5,header=TRUE)
Feb=read.xlsx("Feb 17.xlsx",1,startRow=5,header=TRUE)
Mar=read.xlsx("Mar 17.xlsx",1,startRow=5,header=TRUE)
Apr=read.xlsx("Apr 17.xlsx",1,startRow=5,header=TRUE)
May=read.xlsx("May 17.xlsx",1,startRow=5,header=TRUE)
Jun=read.xlsx("Jun 17.xlsx",1,startRow=5,header=TRUE)

library(plyr)

combine=rbind.fill(Dec,Jan,Feb,Mar,Apr,May,Jun)

[Greg_Deckler]

R to the rescue!

[dilumd]

Yes!!