Forum Discussion

JoaquimPT's avatar
JoaquimPT
Copper Contributor
Jun 19, 2024

Excel Table format change

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 

    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.

    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.

  • Tejas_shah's avatar
    Tejas_shah
    Brass Contributor
    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)<>""))
  • 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.

    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.

    • JoaquimPT's avatar
      JoaquimPT
      Copper Contributor
      Hi Hans Vogelaar,
      Thank you so much for your very clear and useful answer.
      Best regards,
      Joaquim

Resources