Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a csv with account number in the format ##-####.
On import PQ is converting some of these account #'s to dates if the first 2 digits are 1-12 and the last 2 digits are a valid day in the month. the rule isn't exactly that, but close.
I dont have control of the source file.
This behaviour occurs right in the source import.
Example:
02-8514 becomes 14-Feb and was converted to 2/14/2020
02-5882 becomes Feb-85 and was converted to 2/1/1985
02-1490 stays as it should
02-1509 stays as it should
ARGHHH!!!!
All my searches come up with the opposite trying to help me convert text to dates.
How do I stop this behavior it seems so rooted!!!
I know this is built on a lot of excels functionality and it was a problem there.
a;lsdkjf;oiwfenfon!!! [Head smashing keyboard because it seems as productive as this fight with the backend of the app]
If anyone has a work around please let me know.
EDIT - added example converts
Solved! Go to Solution.
Can you share this file ? I tried with the examples but didn't get this behaviour.
Ricardo
Hi @I_Like_Pi ,
Have you checked the steps applied ? You can remove the date converting step (or part of the code).
Ricardo
The first step Source
let
Source = Csv.Document(File.Contents("Testfile.csv"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
generates the behaviour
Can you share this file ? I tried with the examples but didn't get this behaviour.
Ricardo
Thanks for your time, @camargos88
Bad data, and my bad for not checking.
The group that produces the data opened it in excel (to check it) and then saved the convert.
I couldn't give you the file but I thought I could purge it down to the relevant aspects.
Opening in notepad I found records.
I built the extract but I can't run it, and it produces a lot of records,
Sorry for wastin your time.