Excel Power Query scenarios to Load Data from CSV with Mixed Data Types
I've been banging my head against this for hours. I'm experiencing an scenario when trying to load data from a CSV file into Excel using Power Query. The CSV contains a mixture of data types in the same column, specifically dates and text. When I attempt to load the data, I encounter the behavior `DataFormat.behavior: We couldn't parse the input. Input does not match the expected format.`. I've tried manually changing the data type in Power Query before loading, but that results in inconsistent behaviors; for some rows, it seems to convert correctly, while others just default to errors. Here’s a snippet of the M code I’m using to import the CSV: ```m let Source = Csv.Document(File.Contents("C:\path\to\your\file.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]), ChangedType = Table.TransformColumnTypes(Source,{{"DateColumn", type date}, {"TextColumn", type text}}) in ChangedType ``` I’ve checked the CSV file and ensured that there are no leading spaces or unusual characters. The first few rows have dates formatted as `MM/DD/YYYY`, while others contain text like `N/A` or `Unknown`. When I run the query, it seems to unexpected result at the first instance where the `DateColumn` has a non-date entry. I also tried using `try...otherwise` syntax to handle the errors, but it still results in the import failing completely. Is there a way to get Power Query to handle this mixed data type situation? Any suggestions on how to clean the data or configure the query to process it without errors would be greatly appreciated!