SOLVED

# power query - data cleaning and analysis

Occasional Contributor

# power query - data cleaning and analysis

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

# Re: power query - data cleaning and analysis

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

# Re: power query - data cleaning and analysis

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

# Re: power query - data cleaning and analysis

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.

# Re: power query - data cleaning and analysis

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

# Re: power query - data cleaning and analysis

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

best response confirmed by samuel_kodjoe (Occasional Contributor)
Solution

# Re: power query - data cleaning and analysis

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

# Re: power query - data cleaning and analysis

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.

# Re: power query - data cleaning and analysis

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