Moving Text Document Rows to Additional Columns

[Anonymous]

I've got a text report from our Accounting folks that I'm trying to transform in Power Query.  I've cleaned out the junk and am left with this:

Batch 0001 Sample Account 10 Batch 0002 Sample Account 11 Sample Account 12 Batch 0003 Batch 0004 Sample Account 13 Sample Account 14 Sample Account 10

Batch 0005

I would like to move the Sample Account lines to a column alongside their batch with a desired output of

Batch 0001 Sample Account 10

Batch 0002 Sample Account 11

Batch 0002 Sample Account 12

Batch 0003

Batch 0004 Sample Account 13

Batch 0004 Sample Account 14

Batch 0004 Sample Account 10

If that isn't possible, I can make this output work for what I'm doing

Batch 0001 Sample Account 10

Batch 0002 Sample Account 11 Sample Account 12

Batch 0003

Batch 0004 Sample Account 13 Sample Account 14 Sample Account 10

I've tried pivoting and transposing but can't seem to get it exactly right.  Anyone know what to do here?  Thanks in advance!

[Anonymous · Accepted solution]

Hi @Anonymous ,

1. Duplicate the table and follow these steps.

2. Then In Table -> Add a conditinoal column -> filter rows -> merge queries -> remove rows.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[wdx223_Daniel]

=Table.Combine(Table.Group(PreviousStepName,"Column1",{"n",each Table.FromRows({[Column1]})}),0,(x,y)=>Byte.From(Text.StartsWith(y,"Batch")))[n])