cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
IvoryKoss Frequent Visitor
Frequent Visitor

Shift colums value to the next columns??

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Shift colums value to the next columns??

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




6 REPLIES 6
Super User
Super User

Re: Shift colums value to the next columns??

can you share example sheets





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Super User
Super User

Re: Shift colums value to the next columns??

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
IvoryKoss Frequent Visitor
Frequent Visitor

Re: Shift colums value to the next columns??

I added a link to the post.

thx in advance

IvoryKoss Frequent Visitor
Frequent Visitor

Re: Shift colums value to the next columns??

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.

Super User
Super User

Re: Shift colums value to the next columns??

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




IvoryKoss Frequent Visitor
Frequent Visitor

Re: Shift colums value to the next columns??

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?