SOLVED

power query - data cleaning and analysis

%3CLINGO-SUB%20id%3D%22lingo-sub-2354401%22%20slang%3D%22en-US%22%3Epower%20query%20-%20data%20cleaning%20and%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2354401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20Team%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20the%20attached%20excel%20workbook%20contains%20two%20sheet%20namely%20%22system%20data%22%20which%20was%20generated%20from%20a%20system%20and%20the%20second%20sheet%20%22transformed%22%20which%20was%20generated%20using%20%22text%20to%20column%22.%20i%20would%20like%20to%20know%20if%20i%20can%20achieve%20the%20same%20transformation%20using%20power%20query.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2354401%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2354464%22%20slang%3D%22en-US%22%3ERe%3A%20power%20query%20-%20data%20cleaning%20and%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2354464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053787%22%20target%3D%22_blank%22%3E%40samuel_kodjoe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20query%20the%20column%2C%20slit%20text%20by%20position%2C%20change%20types%2C%20return%20table%20into%20sheet%20and%20add%20totals.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2354525%22%20slang%3D%22en-US%22%3ERe%3A%20power%20query%20-%20data%20cleaning%20and%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2354525%22%20slang%3D%22en-US%22%3EWOW!!!%20that%20was%20very%20impressive.%20How%20do%20i%20accurately%20specify%20the%20positions%3F%20it%20seems%20to%20me%20like%20a%20tedious%20task%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2354620%22%20slang%3D%22en-US%22%3ERe%3A%20power%20query%20-%20data%20cleaning%20and%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2354620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053787%22%20target%3D%22_blank%22%3E%40samuel_kodjoe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20did%20that%20by%20couple%20of%20iteration%20and%20approximately%2C%20since%20we%20use%20TRIM()%20exact%20position%20most%20probably%20is%20not%20required.%3C%2FP%3E%0A%3CP%3EHowever%2C%20if%20you%20are%20on%20Excel%20with%20dynamic%20arrays%20you%20add%20in%20couple%20of%20cells%20one%20under%20another%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSEQUENCE(%2CLEN(A2)%2C0)%0Aand%0A%3DMID(A2%2CSEQUENCE(%2CLEN(A2))%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIt%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20463px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280730i9710920C5ACCB247%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPositions%20start%20from%200%20and%20each%20next%20position%20is%20first%20space%20after%20the%20previous%20field.%20That's%20one%20time%20job%20if%20only%20your%20system%20generates%20the%20file%20always%20in%20exactly%20the%20same%20format.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20format%20is%20different%20from%20time%20time%20I%20guess%20removing%20of%20excess%20spaces%20could%20be%20done%20by%20Power%20Query%20but%20that%20will%20require%20M-script%20coding.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2354670%22%20slang%3D%22en-US%22%3ERe%3A%20power%20query%20-%20data%20cleaning%20and%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2354670%22%20slang%3D%22en-US%22%3Ei%20would%20appreciate%20it%20so%20much%20if%20the%20excel%20%22text%20to%20column%22%20feature%20is%20incorporated%20into%20power%20query%3CBR%20%2F%3Ein%20later%20versions.%3C%2FLINGO-BODY%3E
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

@samuel_kodjoe 

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

@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

image.png

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.

@samuel_kodjoe 

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

image.png

best response confirmed by samuel_kodjoe (Occasional Contributor)
Solution

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

Capture 4.JPG

@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

image.png

on

image.png

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