CSVs With Line Breaks In Power BI

Chris Webb talks about one of the banes of my existence:  flat files with “rogue” line breaks:

But what if you can’t fix the source data? I was asked this question the other week, and since I had been asked about it before and not come up with a good answer, I decided to spend some time researching the problem.

What I found was that it was relatively easy to write some M code that gave me the correct results, but very hard to write code that performed acceptably well on a large data set (I was testing on a CSV file containing almost half a million rows). Here’s the code for the function I ended up with:

It’s nice to see that Power Query & Power BI have methods to get around this sort of issue, but it sounds like even those methods are limited in value.

Related Posts

Power Query and the Benefits of Immutability

Chris Webb explains why immutable expressions can be faster to run multiple times than mutable processes: Instead of taking the value #”Sorted Rows”[Column2]{0} and storing it in the variable Column2 then adding Column2 four times, I’m  adding the expression #”Sorted Rows”[Column2]{0} together four times. The query returns the same number as the previous query. However […]

Read More

The Importance of Interaction in Power BI

Marc Lelijveld continues a series on storytelling with Power BI: Many times, I see reports with loads of visuals on the pages. This results in both a really poor performance, as well as the end user has no clue what the key message is of this report. You can always ask yourself, is this visual […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29