Data transormation

[Anonymous]

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

[v-lid-msft · Accepted solution]

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,

[mahoneypat]

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

[jnixon]

Hi,

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

Make sense?

Kind regards,
Jeff