Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
smorzero
New Member

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!

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

3 REPLIES 3
wdx223_Daniel
Super User
Super User

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

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

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):

 

smorzero_0-1675360263739.png

 

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

 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors