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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ashley95e
Helper I
Helper I

Get Data Excwl table causes system date changes

Get Data Excel table causes system date changes - nothing works , even input in Access first causes more problems in BI.

2 ACCEPTED SOLUTIONS

Never mind .

=DATEVALUE(IF(LEN(A7809)=8,(LEFT(A7809,2)&"-"&IF(MID(A7809,3,3)="jly","jul",MID(A7809,3,3))&"-20"&MID(A7809,6,2)),(LEFT(A7809,1)&"-"&IF(MID(A7809,2,3)="jly","jul",MID(A7809,2,3))&"-20"&MID(A7809,5,2))))

 

asHley85e

View solution in original post

After converting to a date structure using Datevalue and are still having issues, add a new column and use = to refer to the Datevalue column. I also Data type as Long Date (I did this to insure no confusion with 2 digit years- '2014 for '14- if there was a problem there it's avoided). After that I converted the table to CSV file to enforce all the assigned data types- When dealing with huge databases I really dont want to revisit this issue!!

 

Example

Column AA has the date value formula                  Column AB references AA

(Datevalue formula)                                                  =AA

 

asHley95e

View solution in original post

4 REPLIES 4
ashley95e
Helper I
Helper I

 

BI and excel are not compatible at all. Any formatting in Excel goes out the window when importing into BI. For instance in prepping my data for BI to avoid problems I elected to clean my data in Excel to minimize having to reformat data in BI (I should mention that the data was not fully created in excel, but a combination of data from different sources)–I used - Trunc(rounded( ))-  in Excel to keep Float data clean at 2 decimal places, and again minimize BI’s formatting . To keep the dates from converting to system 1904  I  used “ =TEXT(S2,"mm/d/yyyy" )” -with all the careful attention to my data to avoid breaking the database, it broke any way, killing all my reports! Now I have to start over from scratch, once the controls break there is no way to recover.    Yeah, I should’ve saved, but I didn’t believe I was doing anything overly complicated.

 

asHley96e -

Never mind .

=DATEVALUE(IF(LEN(A7809)=8,(LEFT(A7809,2)&"-"&IF(MID(A7809,3,3)="jly","jul",MID(A7809,3,3))&"-20"&MID(A7809,6,2)),(LEFT(A7809,1)&"-"&IF(MID(A7809,2,3)="jly","jul",MID(A7809,2,3))&"-20"&MID(A7809,5,2))))

 

asHley85e

After converting to a date structure using Datevalue and are still having issues, add a new column and use = to refer to the Datevalue column. I also Data type as Long Date (I did this to insure no confusion with 2 digit years- '2014 for '14- if there was a problem there it's avoided). After that I converted the table to CSV file to enforce all the assigned data types- When dealing with huge databases I really dont want to revisit this issue!!

 

Example

Column AA has the date value formula                  Column AB references AA

(Datevalue formula)                                                  =AA

 

asHley95e

If you're still having issues save the table as CSV file, so far it appears it forces the column data types you assigned to each column

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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