Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Im trying to split an exported csv file in power query. The split works for thousands of rows, but for some reason, few random rows do not react to the split, despite having the exact same delimiter. Any ideas as to why this is happening?
Solved! Go to Solution.
In my experience, this problem usually occurs because the csv does not have properly quoted delimiters AND one of the fields contain a carriage return or line feed. I get around this issue by opening the file without delimiters and then using the SplitColumn function in PQ. Now you should be able to find the records causing the issue and handle accordingly.
Regards,
Mike
let Source= Table.FromColumns({Lines.FromBinary(File.Contents("C:\Transform Directory\Velocity Reports\12 + Month Reports\Bulk and Count Velocity.txt"), null, null, 1252)}) SplitCol= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"Column1.1", "Column1.2"}) in SplitCol
In my experience, this problem usually occurs because the csv does not have properly quoted delimiters AND one of the fields contain a carriage return or line feed. I get around this issue by opening the file without delimiters and then using the SplitColumn function in PQ. Now you should be able to find the records causing the issue and handle accordingly.
Regards,
Mike
let Source= Table.FromColumns({Lines.FromBinary(File.Contents("C:\Transform Directory\Velocity Reports\12 + Month Reports\Bulk and Count Velocity.txt"), null, null, 1252)}) SplitCol= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"Column1.1", "Column1.2"}) in SplitCol
Hi @Anonymous
Hi @Anonymous
Could you show some sample data or screenshots?
Maybe some rows have space so the function doesn't work well.
If so, try to trim space in Edit queries
https://www.youtube.com/watch?v=g3ws234FRes
https://www.excelguru.ca/blog/2015/10/08/clean-whitespace-in-powerquery/
Please check if you split the column in the right way.
https://www.youtube.com/watch?v=zL_JXB_KfiA
Can you share sample data or PBIX file?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |