PBI randomly set date in Uk or US format. How to solve that.

[TheBaz_57]

Hi there, 

In PBI I have imported several *.csv using import from folder. 

Then first column is "Source.Name" which includes date of the file. 

Patern is: XXXXX.YYYY.MM.DD.XXXXXXX, like ABCD.2023.12.01.DEFGH.

So I have created 3 columns with MID function to extract Year, Month and Day. 

And another column to kind of concatenate the value (using &). And set the format to date. 

So should look like this :

Source.Name	Year	Month	Day	Date
ABCD.2023.12.01.DEFGH	2023	12	01	01 December 2023

But for some dates, not alsways, Powerbi set the last columns like this:

Source.Name	Year	Month	Day	Date
QWERT.2023.12.10.ASDFG	2023	12	10	12 October 2023

I have no idea why PBI does this. Any idea ? 

How can I solve that ?

Thanks all.

[Ahmedx]

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjFFfV6RTnXCvhIie?e=wh8Eqd

[
t1 = Text.PositionOf([Source.Name],".",Occurrence.First),
t2 = Text.PositionOf([Source.Name],".",Occurrence.Last),
t3 = Splitter.SplitTextByPositions({0,t1+1,t2})([Source.Name]){1},
t4 = Date.FromText(t3, [Format = "yyyy.MM.dd", Culture = "en-US"])
][t4]

[speedramps · Accepted solution]

You can change a date column to US or UK format like this 
https://support.microsoft.com/en-us/office/set-a-locale-or-region-for-data-power-query-d42b9390-1fff....

Altetnatively use Power BI question to get each segment YYYY/DD/MM and refomat at YYYY/MM/DD like this

https://www.youtube.com/watch?v=6kKT6QGikMM

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.