Forum Discussion

samuel_kodjoe's avatar
samuel_kodjoe
Copper Contributor
May 14, 2021
Solved

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...
BI & Data Analysis
excel
  • samuel_kodjoe's avatar
    samuel_kodjoe
    May 15, 2021

    i am talking about the fixed width feature as shown belowSergeiBaklan 

samuel_kodjoe's avatar
samuel_kodjoe
Copper Contributor
May 14, 2021
WOW!!! that was very impressive. How do i accurately specify the positions? it seems to me like a tedious task
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 14, 2021

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.

  • samuel_kodjoe's avatar
    samuel_kodjoe
    Copper Contributor
    May 14, 2021
    i would appreciate it so much if the excel "text to column" feature is incorporated into power query
    in later versions.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 15, 2021

      samuel_kodjoe 

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

      • samuel_kodjoe's avatar
        samuel_kodjoe
        Copper Contributor
        May 15, 2021

        i am talking about the fixed width feature as shown belowSergeiBaklan 

Resources