Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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
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):
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!!!!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
9 | |
7 | |
5 | |
5 | |
4 |
User | Count |
---|---|
10 | |
8 | |
7 | |
6 | |
6 |