Excel help

[hussein_elsayed]

Dears,

Could you please help me to get the proper function for the attached sheet, as i want to transpose the data vertically as the below

Unit	Due Date	Amount
18	03/10/2018	6,364.38
18	03/04/2020	5,672.40
18	03/07/2021	768.41

[#2]

@hussein_elsayed 

Hi,

I think the shortest way to do that is with Power Query:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla5"]}[Content],
    #"Columnas quitadas" = Table.RemoveColumns(Origen,{"Client Name"}),
    #"Otras columnas con anulación de dinamización" = Table.UnpivotOtherColumns(#"Columnas quitadas", {"Unit #"}, "Atributo", "Valor"),
    #"Tipo cambiado1" = Table.TransformColumnTypes(#"Otras columnas con anulación de dinamización",{{"Unit #", type text}}),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Tipo cambiado1",{{"Unit #", "Unit"}, {"Atributo", "Due Date"}, {"Valor", "Amount"}}),
    #"Tipo cambiado2" = Table.TransformColumnTypes(#"Columnas con nombre cambiado",{{"Amount", Currency.Type}})
in
    #"Tipo cambiado2"

Unit        Due Date               Amount

18	03/10/2018	6365,38
18	03/04/2020	5672,40
18	03/07/2021	768,41
28	07/12/2018	57,04
28	07/03/2019	141,20
28	07/12/2019	201,31
28	07/03/2020	758,41
28	07/06/2020	15,47
28	07/09/2020	201,02
28	07/03/2021	21493,79
28	07/06/2021	21533,06
28	07/09/2021	4306,61
33	28/11/2019	2127,26
33	28/02/2020	567,58
33	28/05/2020	564,31
33	28/08/2020	416,20
33	28/11/2020	261,03
33	28/02/2021	108,16
33	28/05/2021	103,59

Try it.

Regards,

CarlosF

[#3]

Thank you Mr. Carlos,
Could you please explain how can i do that in a simple steps like you did

[#4 · Accepted solution]

@hussein_elsayed 

First I convert your data into a Excel Table (select all the data and I use Insert->Table).
Second, I used Data->Get Data from Table to open Power Query.
Then:

1. I removed the column "Client Name"
2. Select the column "Unit #", right click on it and select "Unpivot other columns"
3. Finally, I change the data format, columns names

And that's all.

CarlosF

[#5]

Thank you dear, i got it