Forum Discussion
Helper IIIColumn values are shifting after csv files are loaded
lc1 OK, but I need the text. Like, open the file in Notepad. Copy, paste.
Thank you Greg_Deckler , I was able to see the errors when I open the file in Notepad.
Community Championlc1 OK, what are the errors? Have you clicked on "View errors" and investigated? Typically these are due to bad data. For example, let's say you have a Year column with numbers like 2023, 2024, etc. but for some of the rows you have text like "N/A". If the column is set to be numeric versus any, then you will get an error for that cell for that row.
Helper IIIFor example, one of the errors in the Fiscal Year Column is DataFormat.Error: We couldn't convert to Number.
But the data in the csv folder is correct, only when loaded is that I see values entered in the incorrect columns.
- Greg_Deckler
lc1 See my reply below about accounting for separators correctly. What are you using for a separator? If it is space or tab for example, it's really easy to get two spaces or tabs together that might look correct in the file but will shift things in ways that you don't want it to.
- lc1
Greg_Deckler it is separated by ","
- Greg_Deckler
lc1 Can you post the data from an error row as well as a row above and below that row that are OK? My guess is that you need to edit the QuoteStyle in Advanced Editor to QuoteStyle.Csv because it is probably QuoteStyle.None.
Source = Csv.Document(File.Contents("C:\Users\gdeck\OneDrive\Power BI\test1.csv"),[Delimiter=",", Columns=46, Encoding=1252, QuoteStyle=QuoteStyle.Csv])