[FONT="]Hi all,
My raw data looks like this:[/FONT]
[FONT="]The data was copied and pasted from a notepad, so each line is on a different row.
[/FONT]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>[FONT="]
[/FONT]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>[FONT="]- That is, basically, extract text following each common of "20:", "23B:" "32A" & "50K", and organize them to columns.[/FONT]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;"></tbody>[FONT="]2) Once step 1 is solved, I can figure out a way to extract text between 20:/23B:/32A:/50K:, and place it on different columns?[/FONT]
[FONT="]
[/FONT]
[FONT="]Please let me know if this is feasible? What formulas/tools on power query should I utilize?[/FONT]
[FONT="]
[/FONT]
[FONT="]Open to any ideas.[/FONT]
My raw data looks like this:[/FONT]
[FONT="]The data was copied and pasted from a notepad, so each line is on a different row.
[/FONT]
====Starting details===== |
---|
20: abc |
23B: def |
32A: ghk |
50K: lmn |
=====Ending details===== |
====Starting details===== |
20: 123 |
23B: 321 |
32A: 456 |
50K: 789 |
=====Ending details===== |
====Starting details===== |
20: qwe |
23B: wer |
32A: ert |
50K: rty |
=====Ending details===== |
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>
[/FONT]
- Please note that in reality, the details that follow the (20:, 23B:, 32A: 50K are all different lengths, but the details always follow 20:/23B:/32A:/50K:
- Text to column won't work because the data is scattered on different rows.
- I have access to power query
20: | 23B: | 32A: | 50K: |
---|---|---|---|
abc | def | ghk | lmn |
123 | 234 | 456 | 789 |
qwe | wer | ert | rty |
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>
- First of all, should I figure out a way to combine rows between the common texts "====Starting details=====" & "=====Ending details====="? It would become something like this:
20: abc 23B: 321 32A: 456 50K: 789 |
---|
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;"></tbody>
[FONT="]
[/FONT]
[FONT="]Please let me know if this is feasible? What formulas/tools on power query should I utilize?[/FONT]
[FONT="]
[/FONT]
[FONT="]Open to any ideas.[/FONT]