SOLVED

Excel help

%3CLINGO-SUB%20id%3D%22lingo-sub-3261931%22%20slang%3D%22en-US%22%3EExcel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261931%22%20slang%3D%22en-US%22%3E%3CP%3EDears%2C%3C%2FP%3E%3CP%3ECould%20you%20please%20help%20me%20to%20get%20the%20proper%20function%20for%20the%20attached%20sheet%2C%20as%20i%20want%20to%20transpose%20the%20data%20vertically%20as%20the%20below%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EUnit%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDue%20Date%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EAmount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E18%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E03%2F10%2F2018%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E6%2C364.38%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E18%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E03%2F04%2F2020%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E5%2C672.40%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18%3C%2FTD%3E%3CTD%3E03%2F07%2F2021%3C%2FTD%3E%3CTD%3E768.41%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3261931%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261990%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261990%22%20slang%3D%22en-US%22%3EThank%20you%20dear%2C%20i%20got%20it%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261977%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319562%22%20target%3D%22_blank%22%3E%40hussein_elsayed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20I%20convert%20your%20data%20into%20a%20Excel%20Table%20(select%20all%20the%20data%20and%20I%20use%20Insert-%26gt%3BTable).%3CBR%20%2F%3ESecond%2C%20I%20used%20Data-%26gt%3BGet%20Data%20from%20Table%20to%20open%20Power%20Query.%3CBR%20%2F%3EThen%3A%3C%2FP%3E%3COL%3E%3CLI%3EI%20removed%20the%20column%20%22Client%20Name%22%3C%2FLI%3E%3CLI%3ESelect%20the%20column%20%22Unit%20%23%22%2C%20right%20click%20on%20it%20and%20select%20%22Unpivot%20other%20columns%22%3C%2FLI%3E%3CLI%3EFinally%2C%20I%20change%20the%20data%20format%2C%20columns%20names%3C%2FLI%3E%3C%2FOL%3E%3CP%3EAnd%20that's%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECarlosF%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261975%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261975%22%20slang%3D%22en-US%22%3EThank%20you%20Mr.%20Carlos%2C%3CBR%20%2F%3ECould%20you%20please%20explain%20how%20can%20i%20do%20that%20in%20a%20simple%20steps%20like%20you%20did%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261973%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319562%22%20target%3D%22_blank%22%3E%40hussein_elsayed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20shortest%20way%20to%20do%20that%20is%20with%20Power%20Query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%0A%20%20%20%20Origen%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Tabla5%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Columnas%20quitadas%22%20%3D%20Table.RemoveColumns(Origen%2C%7B%22Client%20Name%22%7D)%2C%0A%20%20%20%20%23%22Otras%20columnas%20con%20anulaci%C3%B3n%20de%20dinamizaci%C3%B3n%22%20%3D%20Table.UnpivotOtherColumns(%23%22Columnas%20quitadas%22%2C%20%7B%22Unit%20%23%22%7D%2C%20%22Atributo%22%2C%20%22Valor%22)%2C%0A%20%20%20%20%23%22Tipo%20cambiado1%22%20%3D%20Table.TransformColumnTypes(%23%22Otras%20columnas%20con%20anulaci%C3%B3n%20de%20dinamizaci%C3%B3n%22%2C%7B%7B%22Unit%20%23%22%2C%20type%20text%7D%7D)%2C%0A%20%20%20%20%23%22Columnas%20con%20nombre%20cambiado%22%20%3D%20Table.RenameColumns(%23%22Tipo%20cambiado1%22%2C%7B%7B%22Unit%20%23%22%2C%20%22Unit%22%7D%2C%20%7B%22Atributo%22%2C%20%22Due%20Date%22%7D%2C%20%7B%22Valor%22%2C%20%22Amount%22%7D%7D)%2C%0A%20%20%20%20%23%22Tipo%20cambiado2%22%20%3D%20Table.TransformColumnTypes(%23%22Columnas%20con%20nombre%20cambiado%22%2C%7B%7B%22Amount%22%2C%20Currency.Type%7D%7D)%0Ain%0A%20%20%20%20%23%22Tipo%20cambiado2%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnit%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Due%20Date%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BAmount%3C%2FP%3E%3CTABLE%20width%3D%22282%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E18%3C%2FTD%3E%3CTD%3E03%2F10%2F2018%3C%2FTD%3E%3CTD%3E6365%2C38%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18%3C%2FTD%3E%3CTD%3E03%2F04%2F2020%3C%2FTD%3E%3CTD%3E5672%2C40%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18%3C%2FTD%3E%3CTD%3E03%2F07%2F2021%3C%2FTD%3E%3CTD%3E768%2C41%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F12%2F2018%3C%2FTD%3E%3CTD%3E57%2C04%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F03%2F2019%3C%2FTD%3E%3CTD%3E141%2C20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F12%2F2019%3C%2FTD%3E%3CTD%3E201%2C31%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F03%2F2020%3C%2FTD%3E%3CTD%3E758%2C41%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F06%2F2020%3C%2FTD%3E%3CTD%3E15%2C47%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F09%2F2020%3C%2FTD%3E%3CTD%3E201%2C02%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F03%2F2021%3C%2FTD%3E%3CTD%3E21493%2C79%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F06%2F2021%3C%2FTD%3E%3CTD%3E21533%2C06%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E07%2F09%2F2021%3C%2FTD%3E%3CTD%3E4306%2C61%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3E28%2F11%2F2019%3C%2FTD%3E%3CTD%3E2127%2C26%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3E28%2F02%2F2020%3C%2FTD%3E%3CTD%3E567%2C58%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3E28%2F05%2F2020%3C%2FTD%3E%3CTD%3E564%2C31%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3E28%2F08%2F2020%3C%2FTD%3E%3CTD%3E416%2C20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3E28%2F11%2F2020%3C%2FTD%3E%3CTD%3E261%2C03%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3E28%2F02%2F2021%3C%2FTD%3E%3CTD%3E108%2C16%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3E28%2F05%2F2021%3C%2FTD%3E%3CTD%3E103%2C59%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ECarlosF%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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

UnitDue DateAmount
1803/10/20186,364.38
1803/04/20205,672.40
1803/07/2021768.41
4 Replies

@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

1803/10/20186365,38
1803/04/20205672,40
1803/07/2021768,41
2807/12/201857,04
2807/03/2019141,20
2807/12/2019201,31
2807/03/2020758,41
2807/06/202015,47
2807/09/2020201,02
2807/03/202121493,79
2807/06/202121533,06
2807/09/20214306,61
3328/11/20192127,26
3328/02/2020567,58
3328/05/2020564,31
3328/08/2020416,20
3328/11/2020261,03
3328/02/2021108,16
3328/05/2021103,59

 

Try it.

 

Regards,


CarlosF

Thank you Mr. Carlos,
Could you please explain how can i do that in a simple steps like you did
best response confirmed by Hans Vogelaar (MVP)
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

Thank you dear, i got it