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 

noushad1ali2000's avatar
noushad1ali2000
Copper Contributor
Oct 21, 2024

Greetings,

I am regular watching your vlog related excel, and now i need your help to clean a complex type of data extracted from my company system which i need to clean and provide them in simple form to create some insight from that.

1st sheet as sample extracted

     i have given two sets of data as sample, whereas in my co file having 25860 rows of data for almost 1185-person

2nd sheets need to be normalized

it would be much appreciated if you could help me out

Thanks

Naushad Ali

1st table

Extracted messy data11/11/2012           
ID   818181   role   data practioner 
school              
grade 12            
try              
               
fry              
sum              
code         title    
 121212      jsr      
               
               
               
               
               
________________________________________________________________________________________________________   
ID   919191   role   Sr Data player 
school              
grade 14            
try              
               
fry              
sum              
code         title    
 121212      jsr      
 212121      kel      
               
               
               
               
________________________________________________________________________________________________________   
               

 

2nd table - needed

IDRolegradecodetitle
818181data practioner12121212jsr
919191Sr Data player14121212jsr
   212121kel
  • noushad1ali2000's avatar
    noushad1ali2000
    Copper Contributor
    Oct 22, 2024

    SergeiBaklan  Appreciated, it is really same what i am looking for

     

    Thanks a lot

     

    could you please elaborate little more or could you please suggest some video related to this topic

     

    Thanks once again ..

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 23, 2024

      noushad1ali2000 

      First we remove the row with date of extraction

      and split the rest on the tables having blocks of 15 rows each

      Each table in this list is transformed by function, results are combined.

      What function do is first renames columns with needed information in the table

      , remove all other columns, replace spaces and empty strings on null. Next transform table to columns, in each column remove nulls and combine table back from such columns.

      • noushad1ali2000's avatar
        noushad1ali2000
        Copper Contributor
        Oct 23, 2024

        SergeiBaklan 

        Appreciated

        mind blowing!

         

        I am quite good in macros and data visualization, but I don't have any hesitations to saying, that I need much more class in advance excel. with data model

         

        Thanks

        Naushad Ali

         

Resources