Forum Discussion
Transpose the value
| Ledger | Partner | Debit | Credit | Credit (VAT) |
| Turbine Services & Solutions Aerospace | 3,570.00 | 3,400.00 | 170.00 | |
| Sales A/C | ||||
| VAT SR Output | ||||
| TRICON Trading & Contracting CO. | 525.00 | 500.00 | 25.00 | |
| Sales A/C | ||||
| VAT SR Output | ||||
| WESCO L.L.C | 5,124.00 | 6,100.00 | 244.00 | |
| Sales A/C | ||||
| VAT SR Output | ||||
| NTS Middle East FZCO | 2,756.25 | 2,625.00 | 131.25 | |
| Sales A/C | ||||
| VAT SR Output |
Credit and Credit (Vat) Value should be change to column vise.
please refer the video.
I don't see/understand the rounding issue you refer to. Instead of sharing video or Word doc. could you please:
- Use your actual data
- Refresh the query I shared
- Highlight any rounding issue in the resulting table
and then share the Excel file
20 Replies
- The above link the how to transpose the value is not showing .
The length of the video is 40 sec only.
After the going to the power query what tool I am used to get the desired output.
Could you please guide me .- The above link the how to transpose the value is not showing .
？？
What exactly is your question?
http://e.anyoupin.cn/EData/?s=Transpose
Above is the online tool.
If possible,you can try it.
Something less painful than formulas with Excel 2016, Get & Transform aka Power Query
In attached file:
#1 Put your data in 'Table1'
#2 Switch to tab 'Power Query'
#3 Right-click in the green table > Refresh
one of the issue is faced is that rounding error of the value.
I don't see/understand the rounding issue you refer to. Instead of sharing video or Word doc. could you please:
- Use your actual data
- Refresh the query I shared
- Highlight any rounding issue in the resulting table
and then share the Excel file
I am first time use the power query. Just a few links:
With data formatted as Table named Table1
in G1:
=LET( k, SEQUENCE(ROWS(Table1)), l, MOD(k,3), e, IF(ISBLANK(Table1[[Ledger]:[Debit]]),"",Table1[[Ledger]:[Debit]]), s, INDEX(e,k,SEQUENCE(,3)), VSTACK(Table1[[#Headers],[Ledger]:[Credit]], HSTACK(s, IFS(l=1,"", l=2, INDEX(Table1[Credit],k-1), TRUE,INDEX(Table1[Credit (VAT)],k-2))) ) )- I am not using the updated Microsoft updated version.
I am using 2016 Version.See sheet 'XL 2016' in attached workbook
