SOLVED

Power query Formular or solution

Copper Contributor

I'm seeking for a power query formula that the date on the highlighted Colum to be united in US locate, it 's pattern is US locate stay on left hand side, right hand side are convert from text.   

 

I'm having difficulty on calculating my delivery lead time since the 2 locate is in one column, I have separate them into day, months and year column and merge.

By just doing that I can not define the month and day like 2/1/2022, 8/1/2022 etc. Can I compare the month to beside column  

 

My query formula is still comparing the value, say for example i compare the locate type.  If "delivery lead time" >= 10 it should compare the locate to cells beside and the out is date in US locate. 

 

 

 

 

11 Replies

@hongpon726 That's not a formula. change the data type "Using Locale...", the option at the bottom of the list.

Riny_van_Eekelen_0-1653467372976.png

Now choose Date and English (United States), and press OK.

Riny_van_Eekelen_2-1653467687066.png

 

 

 

@hongpon726 

hongpon726_0-1653469465636.png

 

Yes, because I'm snuggling choose on the locate, I have them all in locate, but however not it is not quite right, for example 1, i want the month keep in December, now it is in Aust.

 

Second Example become Aug. and the other become December 

 

 

 

best response confirmed by hongpon726 (Copper Contributor)
Solution

@hongpon726 Not quite following. You clearly have a mismatch between your system date settings and the format of the dates your trying to import. From your first screenshot it seems that your system settings use dd/mm/yyyy as the date format, as only dates where the second part exceeds 12 turned into texts. On the other hand, the Shipment Date column is set as a date using  the mm/dd/yyyy format.

 

@hongpon726 

hongpon726_2-1653470781619.png

after I choose locate, what is next to do to have the right US locate order please

@hongpon726 Sorry, don't really know how to explain it any further. Perhaps I just don't understand your questions.

Data are mis match, i want to clear up and I use Power query to do that. the first picture was my question, the rest of comment and photo was trying to explaining after the locate setting mm/dd/yy is still mm/dd/yy
Thanks the second text turn's every 12 counts right, i think there is a way to do it right, it should be very common issue, I have over 50,000 of this cell. Please

@hongpon726 

What are your Power Query regional settings?

image.png

And what about source data, are dates mismatched here as well?

@Sergei Baklan 

 

hongpon726_0-1653492294557.png

 

The date is in US 

@hongpon726 

 

hongpon726_0-1653493007636.png

I need those with the time to become US locate , selecting the date and locate result remain the same 

@hongpon726 

So, it looks like you have mismatch in source data where 8/12/2021 means 12th of Aug instead of 08th of Dec. If so afraid Power Query won't help, for it all source data shall be in the same format. 

1 best response

Accepted Solutions
best response confirmed by hongpon726 (Copper Contributor)
Solution

@hongpon726 Not quite following. You clearly have a mismatch between your system date settings and the format of the dates your trying to import. From your first screenshot it seems that your system settings use dd/mm/yyyy as the date format, as only dates where the second part exceeds 12 turned into texts. On the other hand, the Shipment Date column is set as a date using  the mm/dd/yyyy format.

 

View solution in original post