Mar 20 2022 01:41 AM
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 |
Mar 20 2022 03:14 AM - edited Mar 20 2022 03:18 AM
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
Mar 20 2022 03:17 AM
Mar 20 2022 03:29 AM - edited Mar 20 2022 03:30 AM
SolutionFirst 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:
And that's all.
CarlosF
Mar 20 2022 03:29 AM - edited Mar 20 2022 03:30 AM
SolutionFirst 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:
And that's all.
CarlosF