Pastebot line breaks7/25/2023 ![]() ![]() It seemed like any time I referenced the original text (as returned by the Source step) in the List.Generate() function then performance died – so you’ll notice in the code above that I’m only referencing the list that contains the number of delimiters found per line. I’m not really sure why it was so hard to come up with code that performed well, even after liberal use of List.Buffer(). It’s easy to fix the Excel output though: just use the Clean transform to remove the line breaks. I’ve noticed a difference between how the Power BI Desktop and Excel 2016 behave with this code: the current Excel 2016 Query Editor will keep any line breaks after splitting on them, whereas Power BI Desktop does not. ![]() If any of your lines contain extra columns, this approach won’t work.The comments in the code should give you an idea of how it all works. If true then first row contains header values Insert: Find what > A space Replace with > Press Ctrl + J Here, Ctrl + J is the shortcut for character 10, a line break, in the ASCII code. The Find and Replace dialog box pops out. Select C5:C11 and press Ctrl and H keys together. The expected number of commas to be found per line To break lines we can suitably use a particular character to replace it with a line break. Column delimiter character - in this case a comma PromoteHeaders = if UseFirstRowAsHeaders then Table.PromoteHeaders(ConvertToTable) else ConvertToTableĪssuming that you called this function LoadFile(), here’s an example of how to call it: Use the first rows as headers if desired On each line, count the number of column delimiter charactersĮach List.Count(Text.PositionOf(FirstSplit)),ĪctualLines = LineSplitterFunction(Source),ĬonvertToTable = Table.FromList(ActualLines, Splitter.SplitTextByDelimiter(DelimiterCharacter), null, null, ExtraValues.Ignore), ![]() LinePositions = List.Positions(LineBreaks),įirstSplit = Text.Split(Source, "#(cr,lf)"), Get a list of numbers from 0 to the number of line breaks-1 LineBreaks = List.Buffer(Text.PositionOf(Source, "#(cr,lf)", Occurrence.All)), Source = Text.FromBinary(BufferedBinary), (FileName as text, DelimiterCharacter as text, DelimitersPerLine as number, UseFirstRowAsHeaders as logical) =>īufferedBinary = Binary.Buffer(File.Contents(FileName)), ![]()
0 Comments
Leave a Reply. |