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:
- https://support.microsoft.com/en-us/office/getting-started-with-get-transform-in-excel-a8310388-2a12-438c-9d29-c6d29cb8df6a
- https://support.office.com/en-us/article/guide-to-the-power-query-ribbon-05044d36-5261-4219-8324-83adffa27249?ui=en-US&rs=en-US&ad=US
- https://support.microsoft.com/en-us/office/view-and-manage-queries-in-a-workbook-power-query-76f93a6d-37d9-46b5-bc40-d5f2162401f5
- https://support.office.com/en-us/article/microsoft-power-query-for-excel-help-2b433a85-ddfb-420b-9cda-fe0e60b82a94#__tutorials
- https://support.microsoft.com/en-us/office/power-query-101-008b3f46-5b14-4f8b-9a07-d3da689091b5
- https://support.microsoft.com/en-us/office/modify-a-formula-power-query-87c27c8e-1693-476d-8c4e-a83578f66d25
- https://support.microsoft.com/en-us/office/edit-query-step-settings-power-query-3e221afc-c764-4cd9-9a96-a5a5a7688e46
- https://docs.microsoft.com/en-us/powerquery-m/
- https://www.youtube.com/watch?v=NS1AvfV9BeI
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
