Transposing data with multiple fields

[Jranchhod]

Good day

Please can I ask for assistance.

I would like to transpose data I have client information in row format

[TABLE="width: 307"]
<colgroup><col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"><colgroup><col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"><colgroup><col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"><colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"><colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"><tbody>[TR]
[TD="width: 46, bgcolor: transparent"]Name[/TD]
[TD="width: 62, bgcolor: transparent"]Surname[/TD]
[TD="width: 128, bgcolor: transparent"]Home[/TD]
[TD="width: 89, bgcolor: transparent"]Work
[/TD]
[TD="width: 83, bgcolor: transparent"]Cell[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jetash[/TD]
[TD="bgcolor: transparent"]Amith[/TD]
[TD="bgcolor: transparent"]101 205 1256
[/TD]
[TD="bgcolor: transparent"]525 962 3568[/TD]
[TD="bgcolor: transparent"]395 886 9135
[/TD]
[/TR]
</tbody>[/TABLE]

I would like to transpose the information to the following

[TABLE="width: 636"]
<colgroup><col width="212" style="width: 159pt; mso-width-source: userset; mso-width-alt: 7753;" span="4"> <tbody>[TR]
[TD="width: 212, bgcolor: transparent"]Name[/TD]
[TD="width: 212, bgcolor: transparent"]Surname[/TD]
[TD="width: 212, bgcolor: transparent"]Telephone number[/TD]
[TD="width: 212, bgcolor: transparent"]Number type[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jetash[/TD]
[TD="bgcolor: transparent"]Amith[/TD]
[TD="bgcolor: transparent"]101 205 1256[/TD]
[TD="bgcolor: transparent"]Home[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jetash[/TD]
[TD="bgcolor: transparent"]Amith[/TD]
[TD="bgcolor: transparent"]525 962 3568[/TD]
[TD="bgcolor: transparent"]Work[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jetash[/TD]
[TD="bgcolor: transparent"]Amith
[/TD]
[TD="bgcolor: transparent"]395 886 9135[/TD]
[TD="bgcolor: transparent"]Cell[/TD]
[/TR]
</tbody>[/TABLE]

Please can I ask for assistance

Thank you


Jetash

 

[sandy666]

try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Name[/td][td]Surname[/td][td]Home[/td][td]Work[/td][td]Cell[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Surname[/td][td=bgcolor:#70AD47]Telephone Number[/td][td=bgcolor:#70AD47]Number Type[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jetash[/td][td]Amith[/td][td]101 205 1256[/td][td]525 962 3568[/td][td]395 886 9135[/td][td][/td][td=bgcolor:#E2EFDA]Jetash[/td][td=bgcolor:#E2EFDA]Amith[/td][td=bgcolor:#E2EFDA]101 205 1256[/td][td=bgcolor:#E2EFDA]Home[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Jetash[/td][td]Amith[/td][td]525 962 3568[/td][td]Work[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Jetash[/td][td=bgcolor:#E2EFDA]Amith[/td][td=bgcolor:#E2EFDA]395 886 9135[/td][td=bgcolor:#E2EFDA]Cell[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Surname"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Name", "Surname", "Value", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "Telephone Number"}, {"Attribute", "Number Type"}})
in
    #"Renamed Columns"[/SIZE]