I have a pipe delimited text file output from an App that has an Oracle back end. For some reason some rows of data have spurious carriage return/line feed characters in the middle of datetime field entry and this messes up the data import into power query. I cannot do anything about the issue at source and am trying to fix it within power query.

I have a script that seem to fix things (it removes all #(cr)#(lf) characters and then put them back where they are supposed to be), but when I use the "To Table" button it refuse to recognize all of the #(cr)#(lf) characters and combines some of the rows together.

If I copy the text from the Power Query editor at the step before I convert it to a table into Notepad++, then save it as a text file and then connect to that file from Power Query every is correct - I can't see why the "To Table" button is not working in the original script?

my script (without the "To Table" step) is:

let
    Source = Lines.FromBinary(File.Contents("H:\My Documents\test.csv")),
    Listtotext = Text.Combine(Source),
   ReplacedValue2 = Text.Replace((Listtotext),"#(cr)#(lf)", ""),
   SplitText = Text.Split(ReplacedValue2 ," "),
   ListNonBlankValues = List.Select(SplitText,each _<> ""),
   TextJoinList2 = Text.Combine(ListNonBlankValues," "),
   ReplacedValue3 = Text.Replace((TextJoinList2),"|Is Final?", "|Is Final?#(cr)#(lf)"),
   ReplacedValue4 = Text.Replace((ReplacedValue3),"|Y|", "|Y|#(cr)#(lf)"),
   ReplacedValue5 = Text.Replace((ReplacedValue4), "#(cr)#(lf) ", "#(cr)#(lf)")
in
   ReplacedValue5

I have a pipe delimited text file output from an App that has an Oracle back end. For some reason some rows of data have spurious carriage return/line feed characters in the middle of datetime field entry and this messes up the data import into power query. I cannot do anything about the issue at source and am trying to fix it within power query.

I have a script that seem to fix things (it removes all #(cr)#(lf) characters and then put them back where they are supposed to be), but when I use the "To Table" button it refuse to recognize all of the #(cr)#(lf) characters and combines some of the rows together.

If I copy the text from the Power Query editor at the step before I convert it to a table into Notepad++, then save it as a text file and then connect to that file from Power Query every is correct - I can't see why the "To Table" button is not working in the original script?

my script (without the "To Table" step) is:

let
    Source = Lines.FromBinary(File.Contents("H:\My Documents\test.csv")),
    Listtotext = Text.Combine(Source),
   ReplacedValue2 = Text.Replace((Listtotext),"#(cr)#(lf)", ""),
   SplitText = Text.Split(ReplacedValue2 ," "),
   ListNonBlankValues = List.Select(SplitText,each _<> ""),
   TextJoinList2 = Text.Combine(ListNonBlankValues," "),
   ReplacedValue3 = Text.Replace((TextJoinList2),"|Is Final?", "|Is Final?#(cr)#(lf)"),
   ReplacedValue4 = Text.Replace((ReplacedValue3),"|Y|", "|Y|#(cr)#(lf)"),
   ReplacedValue5 = Text.Replace((ReplacedValue4), "#(cr)#(lf) ", "#(cr)#(lf)")
in
   ReplacedValue5

• import
• pipe
• carriage-return
• delimited
• linefeed

1 Answer 1

After many rabbit holes and wrong ways I finally found this worked, it may not be the most efficient or elegant method but it worked for me. I also added some further steps to promote headers and trim the first column (it had a leading space) which I haven't include below but the code here got the data into a table with all the data in the columns they should be in....

let
    Source = Lines.FromBinary(File.Contents("H:\My Documents\test.csv")),
    Listtotext = Text.Combine(Source),
   ReplacedValue2 = Text.Replace((Listtotext),"#(cr)#(lf)", ""),
   SplitText = Text.Split(ReplacedValue2 ," "),
   ListNonBlankValues = List.Select(SplitText,each _<> ""),
   TextJoinList2 = Text.Combine(ListNonBlankValues," "),
   ReplacedValue3 = Text.Replace((TextJoinList2),"|Is Final?", "|Is Final?|^"),
   ReplacedValue4 = Text.Replace((ReplacedValue3),"|Y|", "|Y|^"),
    #"Converted to Table" = #table(1, {{ReplacedValue4}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Converted to Table", {{"Column1", Splitter.SplitTextByDelimiter("^", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29", "Column1.30", "Column1.31", "Column1.32"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}, {"Column1.14", type text}, {"Column1.15", type text}, {"Column1.16", type text}, {"Column1.17", type text}, {"Column1.18", type text}, {"Column1.19", type text}, {"Column1.20", type text}, {"Column1.21", type text}, {"Column1.22", type text}, {"Column1.23", type text}, {"Column1.24", type text}, {"Column1.25", type text}, {"Column1.26", type text}, {"Column1.27", type text}, {"Column1.28", type text}, {"Column1.29", type text}, {"Column1.30", type text}, {"Column1.31", type text}, {"Column1.32", type text}})
in
    #"Changed Type1"