power query - data cleaning and analysis

[samuel_kodjoe]

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.

[#2]

@samuel_kodjoe 

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

Please check in attached.

[#3]

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

[#4]

@samuel_kodjoe 

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

=SEQUENCE(,LEN(A2),0)
and
=MID(A2,SEQUENCE(,LEN(A2)),1)

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.

[#5]

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

[#6]

@samuel_kodjoe 

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

[#7 · Accepted solution]

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

[#8]

@samuel_kodjoe 

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

on

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

[#9]

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