Forum Discussion
How to split one row in more rows
Hi,
We have the below table in excel and we want to split column " Account", and bring the values on rows(keeping for each new row at least the key coulmn).
Tabel in excel
What we need to do is the follwing:
The problem is that we have different delimiter after each number ( "+","-"," ", "(',"(solduri debitoare)").
We need to keep the numbers and text between these delimiters and in the same time bring the row with multiple values to multiple rows(each value in one row), and keep in the same time the column "Key" for each new row.
Do you have any sugestion how I can do this?
Thank you!
| Key | Code | Column | Account |
| Code-70-Column-10 | 0070 | 0010 | ± 201 ± 202 ± 203 ± 204 ± 205 ± 206 ± 209 ± 231 ± 243 + 2513 (solduri debitoare) ± 25171 ± 261 + 2711 (solduri debitoare) ± 27171 ± 272 ± 273 ± 276 ± 277 ± 281 ± 282 ± 283 ± 284 ± 285 - (± 291 ± 292 + 297 ± 299 (solduri aferente activelor financiare)) + ex. 3515 + ex. 35192 ± ex. 355 + 3571 + 371 + 378 ± ex. 381 ± ex. 382 ± ex. 383 ± ex. 384 ± 385 ± 386 - (3921 + 3922 ± 3923 + 3927 ± 3929 (solduri aferente activelor financiare)) + 401 + 402 ± 407 ± 471 ± 481 ± 482 ± 483 ± 484 ± 485 - (4931 + 4932 ± 4933 + 4937 ± 4939 (solduri aferente activelor financiare) ± 494 ± 495 ± 496) + 508 (sold debitor) - 2632 - ex. 3566 - 408 - 4732 |
| Code-70-Column-15 | 0070 | 0015 | ± 201 ± 202 ± 203 ± 204 ± 205 ± 206 ± 209 ± 231 ± 243 + 2513 (solduri debitoare) ± 25171 ± 261 + 2711 (solduri debitoare) ± 27171 ± 272 ± 273 ± 276 ± 277 ± 281 - (± ex. 299 (solduri aferente activelor financiare)) + ex. 3515 + ex. 35192 ± ex. 355 + 3571 + 371 + 378 ± ex. 381 - (± ex. 3929 (solduri aferente activelor financiare)) + 401 + 402 ± 407 ± 471 ± 481 - (± ex. 4939 (solduri aferente activelor financiare) ± ex. 4959) + 508 (sold debitor) - ex. 2632 - ex. 3566 - ex. 408 - ex. 4732 |
| Code-70-Column-30 | 0070 | 0030 | ± 282 ± ex. 285 - (± ex. 299 (solduri aferente activelor financiare)) ± ex. 382 ± ex. 385 ± ex. 386 - (± ex. 3929 (solduri aferente activelor financiare)) ± 482 ± ex. 485 - (± ex. 4939 (solduri aferente activelor financiare) ± ex. 4959) - ex. 2632 - ex. 3566 - ex. 408 - ex. 4732 |
| Code-70-Column-40 | 0070 | 0040 | ± 283 ± ex. 285 - (± ex. 299 (solduri aferente activelor financiare)) ± ex. 383 ± ex. 385 ± ex. 386 - (± ex. 3929 (solduri aferente activelor financiare)) ± ex.483 ± ex. 485 - (± ex. 4939 (solduri aferente activelor financiare) ± ex. 4959) - ex. 2632 - ex. 3566 - ex. 408 - ex. 4732 |
| Code-70-Column-41 | 0070 | 0041 | ± 284 - (± ex. 299 (solduri aferente activelor financiare)) ± ex. 384 - (± ex. 3929 (solduri aferente activelor financiare)) ± 484 - (± ex. 4939 (solduri aferente activelor financiare) ± ex. 4959) - ex. 2632 - ex. 3566 - ex. 408 - ex. 4732 + 2913 (solduri debitoare) + 2923 (solduri debitoare) + 3923 (solduri debitoare) + 4933 (solduri debitoare) + 4943 (solduri debitoare) + 4953 (solduri debitoare) + 4963 (solduri debitoare) |
| Code-70-Column-50 | 0070 | 0050 | - (2911 + 2921 + 3921 + 4931 + 4941 + 4951 + 4961) |
| Code-80-Column-10 | 0080 | 0010 | ± ex. 201 ± 202 ± 203 ± 204 ± 205 ± 206 ± 209 ± 231 ± 243 + 2513 (solduri debitoare) ± 25171 ± 261 ± ex. 281 ± ex. 282 ± ex. 283 ± ex. 284 ± ex. 285 - (± ex. 291 ± ex. 292 + ex. 297 ± ex. 299 (solduri aferente activelor financiare)) - ex. 2632 |
1 Reply
It'll be fairly complex to separate rows with native power query functions; a feasible solution is to integrate python script (by leveraging RegEx object)
