Forum Discussion

mohsin-raza's avatar
mohsin-raza
Helper III
4 years ago
Solved

Conversion/Normalization of data in Tabular form

 

 

Hello:

 

Can some body help me in conversion data in given form into proper tablular form in  power query? Is this related to  the  concept of pivot and unpivot data. 

 

registrationNumberAYH566
archiveStatus0
countryUK
customLabelAYH566 - VPU 330
registrationNumberALG322
archiveStatus0
countryAL
customLabelALG322 - SBLB-129
registrationNumberAKY758
archiveStatus0
countryUSA
customLabelAKY758 - 1648 hdm -
registrationNumberAKA749
archiveStatus0
countryFR
customLabelAKA749 VW  2KN
registrationNumberALK431
archiveStatus0
countryUK
customLabelALK431 -4MNB
registrationNumberAMK483
archiveStatus0
countryUK
customLabelAMK483 -  VPU 330
Need Help
  • SergioSilvaPT's avatar
    SergioSilvaPT
    4 years ago

    Hi mohsin-raza 

     

    You must do the following:

     

    This is your source:

    Click in Add Column -> Index Column and you get this:

     

    Then select Index and (blank) columns and go to Transform and click on Pivot Column :

     

    and you will get this:

     

    Remove the Index column by right clicking and chosing Remove Column.

    It's done!

     

    You can replace all the null with blank by selecting all columns and go to Transform -> Replace Values

     

    Regards,

    Sérgio Silva 

     
    If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

     

5 Replies

  • SergioSilvaPT's avatar
    SergioSilvaPT
    Resolver V
    4 years ago

    Hi mohsin-raza 

     

    You must do the following:

     

    This is your source:

    Click in Add Column -> Index Column and you get this:

     

    Then select Index and (blank) columns and go to Transform and click on Pivot Column :

     

    and you will get this:

     

    Remove the Index column by right clicking and chosing Remove Column.

    It's done!

     

    You can replace all the null with blank by selecting all columns and go to Transform -> Replace Values

     

    Regards,

    Sérgio Silva 

     
    If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

     

    • mohsin-raza's avatar
      mohsin-raza
      Helper III
      4 years ago

      SergioSilvaPT 

       Thanks for the solution . It looks very fine with the result you produced .

       

      In step 2 when i select blank and index and chose Pivot column . I am not getting the same result. Pivot column also ask for choice of column ?

      On selecting  any of the column  i am not getting the same answer..

      can you see  it?

       

       and