Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data transormation

Hi all,

Need some tips and explanation about how to do this.


I'm extracting data from several excel sheets (and multiple workbooks) : 

 

 

 

 

When i expand this column, i get somethink like this.

 

 

 

 

I'm 
And this is what i'm expecting : 

 

 

 

 

 

 

 

Thanks

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can try to add a custom column:

     

    Table.PromoteHeaders(Table.Transpose([NameOfYourSecondColumn]), [PromoteAllScalars=true])

     

    Then delete the old column and expand the new table column to meet your requirement.

     


    Best regards,

     

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can try to add a custom column:

     

    Table.PromoteHeaders(Table.Transpose([NameOfYourSecondColumn]), [PromoteAllScalars=true])

     

    Then delete the old column and expand the new table column to meet your requirement.

     


    Best regards,

     

  • jnixon's avatar
    jnixon
    Advocate II
    6 years ago

    Hi,

     

    Try selecting first two columns, unpivot other columns, then select Date column and Pivot that one.

     

    Make sense?

     

    Kind regards,
    Jeff

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    For this one, some data transformation should be done before you expand all the Tables.  That can be done with Combine & Edit or writing a custom function to do it.  Transforming the data expanded as you've shown is probably doable but would be tricky.  If you can post a link to the Excel sheet, we can propose the needed query (and function) to transform the tables before the append step.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat