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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
yhuang1
Frequent Visitor

Read csv file date field in it's original value

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

1 ACCEPTED SOLUTION
SamInogic
Super User
Super User

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))

 

SamInogic_1-1680159241593.png

 

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

View solution in original post

2 REPLIES 2
yhuang1
Frequent Visitor

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
Super User
Super User

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))

 

SamInogic_1-1680159241593.png

 

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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