SOLVED

Excel Table format change

Copper Contributor

Hi,

I need to convert a table format as per picture below (table with hundreds of lines).

Any suggestion to automate this kind of table transformation?

Many Thanks.

Joaquim

 

JoaquimPT_1-1718786784596.png

 

3 Replies
best response confirmed by JoaquimPT (Copper Contributor)
Solution

@JoaquimPT 

You can use Power Query for this.

Click anywhere in the table.

On the Data tab of the ribbon, in the Get & Transform Data group, click From Table/Range.

In the Power Query Editor, activate the Transform tab of the ribbon.

Click the drop-down arrow on the right of Unpivot Columns, and select Unpivot Other Columns.

HansVogelaar_1-1718789169638.png

Select the Attribute column, right-click in it and select Remove from the context menu.

Double-click Value and change it to Contact.

Activate the Home tab of the ribbon.

Click Close & Load > Close & Load to..., and select a destination.

HansVogelaar_2-1718789338536.png

Hi Hans Vogelaar,
Thank you so much for your very clear and useful answer.
Best regards,
Joaquim
Excel 365 Version
Data starts from A1
You can use the below formula
LET(TBL,TEXTSPLIT(TEXTJOIN(",",TRUE,(TOCOL(A2:A4&"|"&B2:D4,0,FALSE))),"|",","),FILTER(TBL,CHOOSECOLS(TBL,2)<>""))
1 best response

Accepted Solutions
best response confirmed by JoaquimPT (Copper Contributor)
Solution

@JoaquimPT 

You can use Power Query for this.

Click anywhere in the table.

On the Data tab of the ribbon, in the Get & Transform Data group, click From Table/Range.

In the Power Query Editor, activate the Transform tab of the ribbon.

Click the drop-down arrow on the right of Unpivot Columns, and select Unpivot Other Columns.

HansVogelaar_1-1718789169638.png

Select the Attribute column, right-click in it and select Remove from the context menu.

Double-click Value and change it to Contact.

Activate the Home tab of the ribbon.

Click Close & Load > Close & Load to..., and select a destination.

HansVogelaar_2-1718789338536.png

View solution in original post