cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syaifulefendi Helper III
Helper III

0 Value from Date Column Result 12/31/1899

Dear Team

 

Need urgent Help

Let say, i have an excel with column name 'FREE TIME END" contain of date value .

i got this value from vlookup from another workbook.

if workbook target has no value , mean result "0"

easy to hide 0 as blank on excel .

 

After i GetData from my Excel with FREE TIME END , all "0" value return to 12/31/1899

also i cant to change data type from "text" to "date" format

what i need to do ?

this is my excel view

FT2.pngMy Excel

And below my powerbi data view

FT1.pngMy Powerbi

 

How do i eliminate all 12/31/1899 as blank .

 

Thanks

syaiful

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: 0 Value from Date Column Result 12/31/1899

Hi,

 

Try using the replace value in power query editor to replace the unwanted dates with blank

 

awdawd.PNG

View solution in original post

4 REPLIES 4
Super User I
Super User I

Re: 0 Value from Date Column Result 12/31/1899

Hi,

 

Try using the replace value in power query editor to replace the unwanted dates with blank

 

awdawd.PNG

View solution in original post

epappu Resolver I
Resolver I

Re: 0 Value from Date Column Result 12/31/1899

Hi there - 

 

Can you please share the input file? I tried to create an input file similar to yours but I am able to get the data correctly in PowerBI.

 

Regards

Eswar

epappu Resolver I
Resolver I

Re: 0 Value from Date Column Result 12/31/1899

Hi syaiful,

 

Just as an afterthought... can you please check the following?

 

In PowerBI desktop, go to File --> Options and Settings, Then to "Current File" --> Regional Settings - Check what do you have selected for "Locale for Import", if this matches to the same locale for your Excel file, well and good, otherwise I think you need to change this in order to get the same date format as you have in Excel to start with. Hope this helps. Once I have the sample Excel file from you, that may help a bit more to check it out.

 

Regards

E

Highlighted
sduffy Helper I
Helper I

Re: 0 Value from Date Column Result 12/31/1899

I too, was unable to get the data to display 'Incorrectly' in Power BI.  As Eswar suggests, this may be to do with your input.

 

FreeTime.PNG

 

regards, Sduffy

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors