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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.