Replace a set of date/time entries

[Anonymous]

Hello. I have a set of excel files which I compile in Access in order to analyse them. After append the 5 files into a access table, I import to Power Query in order to manipulate them. 

The problem is the data format, it's not the same in the 5 files. One of the errors is in a Date Column.  This date column is in text format but when I convert to date/time format, I have a lot of errors. In the Date Column, I have fields with date and time, only time and these kind of stuff:

Date_Column

2020-06-19 17:27:51.130000000

First: I want to convert all of these rows that have 2020-06-19 17:27:51.130000000 format to 2020-06-19 17:27:51. So, remove the numbers after the dot. 

Second: Convert all entries into Date/time format even with the only date entries.

Anyone can help me?

Thank you!

[Anonymous]

I open the query in data model and verified that I have blanks in date column. I converted to text and replace the blanks with null but nothing happen. The errors are here because the number of blanks is the same number of errors in power query. Any help?

[Anonymous]

The error is: DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
13/01/2020

[edhans]

Again @Anonymous can you please provide some actual data instead of one off examples of what works and what fails? Otherwise we will be playing whack-a-mole as you post back with another date that fails.

As for the date 13/01/2020 my original formula try Date.FromText([Date]) otherwise null will not return an error. It will return null, because Date.FromText() does return an error as it is not expecting a date in that format. But this will work:

try Date.FromText([Date]) otherwise try Date.FromText([Date], "en-BS") otherwise null

"en-BS" tells Power Query to use the Bahamas English culture to do the conversion, which is DD/MM/YYYY vs "en-US" which is MM/DD/YYYY.

Again, if you could provide a single column of perhaps half a dozen or more of these various date formats, it would make our task easier to solve this, and you will get a comprehensive answer.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Sorry for my late response and without the some of the data source. Here's an example: 

I tried your try formula but only appeared null fields. 

Thank you!

@edhans 

[edhans]

Looks simple enough. If you could please put that in a format I can use. I am not typing that in. I'll post the links again that explain how to share data here. Images are great for "this is what I want it to look like when done" but horrible for "here is an image of 50 numbers, can you type all of that in manually on your side then give me an answer?" 😁

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

This is the source data from the 10 entries that appears an error in the secreenshoots that I post. I'm dealing with an access with over 1 millon of entries and i'm trying to solve this since yesterday without success. 

Thank you.

@edhans 

DataEncomenda	DataPicking	DataFactura
16/02/2019 13:53:01	2019-02-18	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-18	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-18	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-18	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-20	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-18	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-18	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-18	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-19	20/02/2019 14:55:00
16/02/2019 13:53:01	2019-02-18	20/02/2019 14:55:00

[edhans]

Understood @Anonymous - not trying to be difficult, just need clean data. Thanks for your post. So here is what I have:

My M code for this is as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTTNzDSNzIwtFQwNLYyNbYyMFTSUQLxdQ2MdA0twByEEhMrU1MrAwOlWJ1hp9XIYIg5mBKtljSwNRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DataEncomenda = _t, DataPicking = _t, DataFactura = _t]),
    #"Added Custom" = Table.AddColumn(Source, "DataEconomenda Fix", each try DateTime.Date(DateTime.FromText([DataEncomenda], "en-BS"))otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DataPicking Fix", each try Date.FromText([DataPicking]) otherwise null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "DataFractura Fix", each try DateTime.Date(DateTime.FromText([DataFactura], "en-BS"))otherwise null)
in
    #"Added Custom2"

Note that I am US English, and my Power Query Culture Code is en-US. You can get yours by creating a new blank query and simply typing = Culture.Current in the formula bar. I should hvae asked for yours earlier.

So Power Query for me will error out if it gets dates in DD/MM/YYYY format unless I tell it otherwise, which I did by using the Bahama's culture code of en-BS which recognizes that vs the standard US MM/DD/YYYY code.

I think we were going for dates, right? So all of my code strips out the time codes if they exist.

The key is, you want to spend time with:

• Date.FromText([FieldName], "en-BS") if the date is DD/MM/YYYY and you need MM/DD/YYYY.
• DateTime.FromText(same as above) if the code has DateTime formats.
• DateTime.Date() to wrap around the above formula to ensure you strip out the time and get back to a pure date.

If you have more questions or other oddities, post back with more data.

If you always use DateTime.Date(DateTime.FromText([YourColumnHere)) you will be ok too. If time is missing from the text, it will return midnight. But if time exists and you just use Date.FromText() it will bomb as that is not expecting time.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

Hello. Many thanks for your answer. 

I already tried one solution (DateTime.Date(DateTime.FromText([YourColumnHere))) with "pt-PT" and parcially worked. There were no errors but the hour:minute:second appears 00:00:00 when my original data has hour:minute:second fields. Any idea? Thank you.

[edhans · Accepted solution]

Hi @Anonymous, let me see if I understand. You are using this formula:

= DateTime.Date(DateTime.FromText([YourColumnHere], "pt-PT"))

But your issue is the hours/min/sec are all zeros even if they exist.

If I have that right, here is why.

1. The outer function, DateTime.Date() stripped all time from the field after DateTime.FromText() got it.
2. You later formatted that custom column as type datetime, so it automatically puts the time out there, and if none, it will have 12am with zeros for minutes and seconds.

You could trap this with something like this in a new Custom Column:

let
     varDateTime = DateTime.FromText([DataEncomenda], "pt-PT"),
     varDate = DateTime.Date(varDateTime)
in
     if varDateTime = DateTime.From(varDate)
     then varDate
     else varDateTime

It returns this from your sample data that I tweaked on the first two rows. I used variables above because I didn't want to type those functions in a dozen times.

What my formula did is:

• let it do the DateTime.FromText() conversion - stored in varDateTime
• Then converted to date only with DateTime.Date(varDateTime) - stored in varDate
• Then I compared the varDateTime to varDate converte back to Datetime - using DateTime.From(varDate)
• If varDateTime has bunch of zeros, then they are the same, because DateTime.From(varDate) has no time, so just zeros.
• If they are different, then varDateTime must have valid time codes.

I keep varDateTime if there are valid times, and keep varDate if there are none or the times are all zeros.

Make sense?
Also note that If I then format that final column as DateTime, PQ will put the zero times back in. It essentially does a DateTime.From() on the entire column.

[edhans]

You will need to this in a few steps. Assume your original data is in [Date_Column]

To get rid of everything before the dots, select the Transform menu, then Extract and set it up this way: (you cannot see it clearly in the image, but there is a period in the delimiter box below)

Everything to the left of that last period will be retained.

For your dates, you didn't provide a comprehensive list of issues, but the following formula will fix three scenarios:

try DateTime.FromText([Date Column]) otherwise null

You can see the date/time and date worked, with the latter getting 12am. Now you can convert this new field to date only.

For the time, what do you want to do? There is no way to convert time to a date. Power Query assumed it was time, and correctly did that, then slapped today's date there.

If you need more help, please give us a really good sample of the actual dates. Use the links below for providing this. DOn't post screen caps of your data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[mahoneypat]

Does it work to extract text before delimiter?  Use the space if all the columns have that and you want just Date.  Or you can extract before the ".", if you need DateTime.  You can then convert the column the Date or Time after that.  If more is needed, please paste examples of all the values.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat