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...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    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

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

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
3 years ago

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