Shift colums value to the next columns??

[IvoryKoss]

Good Day,

While importing/merging multiple sheets from an excel file, I realised that :

- the column names and the columns order are the same in all the sheets (what is good as I want to merge all of them)

- but in some sheets the starting cell differs. i.e : almost all sheet tables  start at cell A1, but some start at B1

Is it possible with M language to set a condition on a column values and if respected , all the values of will be shifted by 1 column???

Example:

https://drive.google.com/file/d/0B_gA_LQC4vQhMkVUbjZnd1pLREE/view?usp=sharing

On sheet 3 , the table is shifted. But I still want to have a perfect merging

[MattAllington]

Probabaly not as you describe. Any reason why you can't delete the blank column A:A and be done with it?  

I think if you selected one of the workbooks starting at B1 as the sample to combine, it could work. After the column headers are promoted, there should be a blank column in the first column. When the other workbooks are processed, they won't have this blank column. But all other columns should match. When they are all appended it should give a superset of all columns including the blank one. Just delete the blank column as the last step before load. 

[IvoryKoss]

Thx Matt for your answer.

I want all the columns to match  as final result. I ve added a sample . Thank you for your support.

[parry2k · Accepted solution]

https://drive.google.com/file/d/0B4dPgH9_BPBrbnR3bmJzUEJEajA/view?usp=sharing

I created a function which will reach all sheets from excel file. You can provide excel file name  in the parameter.

In step3 (Expanded GetSheetData) of "MyData" table called, you can select the columns you want, if sheets has more column or blank, only selected column will get combined.

Thanks,

P

[IvoryKoss]

Hi Parry2k,

it works perfectly with the sample worksheets.

I changed the parameters to apply it to my main worksheet and few challenges happened:

1- The worksheet has 52 sheets to be passed trough the function, and it takes a while to compute with my core i5, 2,6Ghz (5minutes+)

When I just extract in bulk it takes less than a minute. Any way I can optimize?

[parry2k]

can you share example sheets

[IvoryKoss]

I added a link to the post.

thx in advance