Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
I_Like_Pi
Resolver II
Resolver II

PQ is converting an account number to a date. Help Please

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

1 ACCEPTED SOLUTION

@I_Like_Pi ,

 

Can you share this file ? I tried with the examples but didn't get this behaviour.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

4 REPLIES 4
camargos88
Community Champion
Community Champion

Hi @I_Like_Pi ,

 

Have you checked the steps applied ? You can remove the date converting step (or part of the code).

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



The first step Source

let
Source = Csv.Document(File.Contents("Testfile.csv"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),

 

generates the behaviour

@I_Like_Pi ,

 

Can you share this file ? I tried with the examples but didn't get this behaviour.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors