Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am importing birthdates from a text file into PowerQuery. The birthdates in the text file are a two digit year format (e.g. 7/20/69). When reviewing the imported birthdates in PowerQuery, four digit years are shown and it appears that birthdates prior to 1/1/1950 are converted to a 21st century date instead of a 20th century date. For example, a birthdate of 12/7/41 appears as 12/7/2041 when it should appear 12/7/1941. Thanks very much for any help!
Solved! Go to Solution.
This is the problem with 2 digit years @mstone3
You can try this custom column formula. Leave those dates as text, and create this column:
let
varYear = Number.From(Text.AfterDelimiter([Date], "/", 1))
in
Date.From(
Text.BeforeDelimiter([Date], "/", 1)
& "/"
& Text.From(
(if varYear > 20 then 1900 else 2000) + varYear)
)
Any date after 20 will be counted as the year 2000 or later, but that could be wrong. You can change it. But this logic will not allow you to have a 107 year old and a 7 yr old at the same time. 1/1/15 - is that 1915 or 2015? But depending you your data you can make that decision. Patients would be difficult for a hospital as they could have both, but employees would never have a 7yr old so you could adjust accordingly. Of course, not too many 107 yr old employees either. 😁
Then remove your orginal Date column
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis is the problem with 2 digit years @mstone3
You can try this custom column formula. Leave those dates as text, and create this column:
let
varYear = Number.From(Text.AfterDelimiter([Date], "/", 1))
in
Date.From(
Text.BeforeDelimiter([Date], "/", 1)
& "/"
& Text.From(
(if varYear > 20 then 1900 else 2000) + varYear)
)
Any date after 20 will be counted as the year 2000 or later, but that could be wrong. You can change it. But this logic will not allow you to have a 107 year old and a 7 yr old at the same time. 1/1/15 - is that 1915 or 2015? But depending you your data you can make that decision. Patients would be difficult for a hospital as they could have both, but employees would never have a 7yr old so you could adjust accordingly. Of course, not too many 107 yr old employees either. 😁
Then remove your orginal Date column
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you!
Glad it helped @mstone3 👍
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.