Forum Discussion

smorzero's avatar
smorzero
New Member
3 years ago
Solved

Counting Columns and adding new row after delimiter is detected

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!

  • 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

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion

    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's avatar
      smorzero
      New Member

      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!!!!

       

       

       

    • smorzero's avatar
      smorzero
      New Member

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