Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello guys,
I need your help with converting a text column towards a data type.
Should i add a column with a certain formula to achieve this?
See below the error that I'm getting from this:
Many thanks!!!
Cheers,
DSabsi
Solved! Go to Solution.
Sure (I included some Dutch as well 🙂 😞
let Source = #table(type table[Verwachte_Start = text],{{"1499032800"},{"1498860000"},{"1493589600"},{"1491170400"}}), AddedUTCDateTime = Table.AddColumn(Source, "Verwachte_startdatum/tijd_UTC", each #datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,Number.From([Verwachte_Start])), type datetimezone), AddedLocalDate = Table.AddColumn(AddedUTCDateTime, "Verwachte_startdatum_lokaal", each Date.From([#"Verwachte_startdatum/tijd_UTC"]), type date) in AddedLocalDate
Hi All,
I'm stuck at creating a custom date column.
So the thing is i have a date column which is in text datatype as following 09-01-24 (which should be date as in 9 January 2024) and when converting into date it gives error on some dates.
How can i fix this in pq?
Hi @dsabsi,
It will be help if you share some sample data or date structure.
Regards,
Xiaoxin Sheng
Hi Guys @v-shex-msft@SivaMani@MarcelBeug@blopez11,
I found out that the text value is an epoch value in a text value.
Do you know how to convert an epoch value in a text format to dates value in date format?
Cheers,
DSabsi
Sure (I included some Dutch as well 🙂 😞
let Source = #table(type table[Verwachte_Start = text],{{"1499032800"},{"1498860000"},{"1493589600"},{"1491170400"}}), AddedUTCDateTime = Table.AddColumn(Source, "Verwachte_startdatum/tijd_UTC", each #datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,Number.From([Verwachte_Start])), type datetimezone), AddedLocalDate = Table.AddColumn(AddedUTCDateTime, "Verwachte_startdatum_lokaal", each Date.From([#"Verwachte_startdatum/tijd_UTC"]), type date) in AddedLocalDate
Hi @MarcelBeug,
Thank you for your solution, you're great!
I also learned that an alternative to this is:
1. Convert the text to a number by the following formula (creat new column) : Verwachte start number = value([verwachte_start] )/(60*60*24)
2. Convert the value to a year date format (create new column) : Verwachte StartJaar = YEAR(IF([Verwachte start number] = BLANK() ;BLANK();DATEVALUE("01/01/1970") + [Verwachte start number]))
Cheers,
DSabsi
Hi @MarcelBeug,
Thank you for your answer (and of course in Dutch 😉 I appreciate that.
Should I try your formula in a column or through edit-queries?
Cheers,
DSabsi
My solutions are always (99.9%) via Edit Queries.
HI @dsabsi
Go to query editor and create custom column with the following formula,
Date.FromText([DateField],Corresponding culture code)
Regards,
Siva
@MarcelBeug In my experience, Power BI's default date format is MM/DD/YYYY - Because of Locale(by default) in English(United State).
GB's date format is DD/MM/YYYY.
According to your sample date formats,
4th (2017-04-01) and 6th(4/1/2017) formats will be detected as a date by Power BI.
The Rest of them, we have to convert it to the required format. Since it is in English(GB) format. So I have used "en-GB"
I hope I've made it clear enough.
If you have anything apart from this, just tell me it will be helpful.
Thanks,
Siva
The default depends on your Regional Settings in Windows when you create a new Power BI Desktop file.
Again: we didn't get any information how the dates look like, so we just don't know and can only guess.
Just to give you an idea: there are almost 600 different culture codes, that may influence formats (dates, numbers, of course names of days and months) and even the A-Z sort order, e.g. Hawaii puts the vowels up front:
Otherwise of course not all cultures use the characters A-Z as their alphabet.
Can you give us a sample of what your text data looks like and what data type you are trying to convert it to? That would help in providing an answer
Hello guys,
My apologies for my late response.
Hereby the sample of the data that I got from my boss:
The format is text and I wish to change it to dates.
I hope you can help me, I'm really astonished by the amount of replies (many thanks!!!!)
Cheers,
DSabsi