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

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