power query - data cleaning and analysis

Occasional Contributor

Hi Team,

Please the attached excel workbook contains two sheet namely "system data" which was generated from a system and the second sheet "transformed" which was generated using "text to column". i would like to know if i can achieve the same transformation using power query.

8 Replies


You may query the column, slit text by position, change types, return table into sheet and add totals.


Please check in attached.

WOW!!! that was very impressive. How do i accurately specify the positions? it seems to me like a tedious task


I did that by couple of iteration and approximately, since we use TRIM() exact position most probably is not required.

However, if you are on Excel with dynamic arrays you add in couple of cells one under another


It looks like


Positions start from 0 and each next position is first space after the previous field. That's one time job if only your system generates the file always in exactly the same format.


If format is different from time time I guess removing of excess spaces could be done by Power Query but that will require M-script coding.

i would appreciate it so much if the excel "text to column" feature is incorporated into power query
in later versions.


Actually that is much more powerful Table.SplitColumn() function which could be called from here


best response confirmed by samuel_kodjoe (Occasional Contributor)

i am talking about the fixed width feature as shown below@Sergei Baklan 

Capture 4.JPG


In your case it's not very needed. As variant, you may select Split from Non-digit to digit and in formula bar change




Result will be the same as you split by positions the sample we discussed.

Your method worked perfectly but failed to split the headings. i also applied the same method to a different data with the same problem and the results were not up to expectation but with "text to column" it worked in all the different data it was applied to. @Sergei Baklan