Read csv file date field in it's original value

[yhuang1]

Hi there,

I have a csv file, one of the columns is a date. If I open the csv file in a text editor, I can see the raw value as '2021-11-17'. in some of the rows the values are blank, PBI desktop data loader read this column and automatically convert it date type, but I really need it to be text,  so that when the date is a partial, something like '2022-03-UN', it can retain the raw value. If I change it to text type after it already read in as date, it changes to '17NOV2021'. I also tried to load it with "Do not detect data types" option selected, it does read it as text, but showing a value of 17NOV2021 instead of 2021-11-17. Is there a way I can force it to read the original raw value as text type, with value of 2021-11-17?

Thanks

Ya

[yhuang1]

Sorry, my bad. I just realized that I was looking at the wrong file. The one with 2021-11-17 is the real raw data file sitting in a folder different from the one actually loaded to PBI. Both files are named the same, except that the one loaded to PBI is preprocessed, somehow the preprocess changed 2021-11-07 to 17NOV2021, mystery solved.

Still I want to thank SamInogic for his comments.

[SamInogic · Accepted solution]

Hi,

As per our understanding, You are looking for a date field in its original value coming from CSV file,

if PBI is detecting the Datatype and making it to DATE,
And you are looking for a format '2021-11-17' from '17NOV2021',
For achieving this you can use CONCATENATE with the Date column

Result =

var day_c = 'Table'[Csv_Date].[Day]

var month_c =
CONCATENATE('Table'[Csv_Date].[MonthNo],"-")

var year_c =
CONCATENATE('Table'[Csv_Date].[Year],"-")

return CONCATENATE(year_c,CONCATENATE(month_c,day_c))

If the error still remains help us by Providing PBIX or CSV file to hide Sensitive data.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

View solution in original post