Counting Columns and adding new row after delimiter is detected

[smorzero]

Hi,

I am attempting to import a text file that contains a large amount of rows.  Without manually putting the rows into columns and adding new rows, I would like to use the power query.  Ex. here is the text file:

row1

row2

row3

row4

row5

row6

row7

;

rowXX

rowXX

rowXX

rowXX

rowXX

rowXX

rowXX

;

.... (1200 more times)

For each row, I would like to clear the blank spaces (which I accomplished).  For each row I would like to put into a column, then at each semicolon I would like to break it off into a new row and do the same for the next 1200 more rows.  Ex.

With power query editor, I would like to accomplish: (* note each column would be exactly a count of 7 and repeated 1200 more times)

row1    row2   row3   row4   row5    row6   row7  <semicolon would be here but no column for it>

rowXX rowXX rowXX rowXX rowXX rowXX rowXX

rowXX rowXX rowXX rowXX rowXX rowXX rowXX

rowXX rowXX rowXX rowXX rowXX rowXX rowXX

..... <1200 more times>

Here is what I have but I can't do anything more that this.  I tried putting the row into columns and break the columns into rows after each semicolon but no success.

let
   Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\<username>\Documents\textname.txt"), null, null, 1252)}),
   #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
   #"Transposed Table" = Table.Transpose(#"Removed Blank Rows"),
   #"Count Columns" = Calculate(Table.countcolumns(#"Transposed Table")) > 7,            ///// <--- no success
   #"New Row" = Table.AddRow(#"Count Columns") + 1                                                   ///// <--- no success
in
   #"Transposed Table"

I appreciate the help!

[wdx223_Daniel · Accepted solution]

let
   Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\<username>\Documents\textname.txt"), null, null, 1252)}),
   #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
  Custom1=Table.Combine(Table.Group(#"Removed Blank Rows",Table.ColumNames(#"Removed Blank Rows"){0},{"n",each Table.Transpose(Table.Skip(_,each Record.ToList(_){0}=";"))},0,(x,y)=>Byte.From(y=";"))[n])
in
  Custom1

[smorzero]

Nevermind, I found the error after the 100th time looking at it.  ColumnNames.  You're the Jedi Master!  I appreciate the help!!!!  Thank you!

[smorzero]

Hi wdx223_Daniel,

First of all, wow this is a complex syntax.  I appreciate the help, but there is something that I'm not doing right.  I copied the script into the Power Query Editor but receive an error (I changed to the correct text document and username):

I attempted to go through the Custom1 but I don't think I come close to knowing what the problem is.  Can you assist again?  I appreciate your help!!!!