Calculation birthday from social security number (danish)

[Niels_Andersen]

Hi,

I need to calculate the birthday of the people in my dataset from their social security number. This is a 10-digit number where the first 6 digits date of birth. for example, 051289XXXX would be 5th of December and (probably) 1989.
However because only 2 digits are used to describe year, I need to use the following 4 digits to determine century of birth.

This can be determined as follows

• If the 7th digit is 0, 1, 2 or 3 the person is from the 19th century
• If the 7th digit is 4 or 9 and the year indicator in the first 6 digits are <36 the person is from the 19th century. If they are =<36 the person is from the 20th century.
• If the 7th digit is 5, 6, 7 or 8 and the year indicator in the first 6 digits are =>36 the person is from the 20th century. If they are <=58 the person is from the 19th century.

In excel I would do something like this. But how do is express this in PowerBi advanced editor?

Public Function CprToDato(cpr As String) As Date

    Dim bytCent As Byte
    Dim bytSevdig As Byte
    Dim bytCprYear As String

    If Not IsNull(cpr) Then

        bytSevdig = Mid(cpr, 8, 1)
        bytCprYear = Mid(cpr, 5, 2)

        Select Case bytSevdig
            Case 0 To 3
                bytCent = 19
            Case 4, 9
                If bytCprYear <= 36 Then
                    bytCent = 20
                Else
                    bytCent = 19
                End If
            Case 5 To 8
                If bytCprYear <= 36 Then
                    bytCent = 20
                ElseIf bytCprYear >= 58 Then
                    bytCent = 18
                End If
        End Select

        CprToDato = Left(cpr, 2) & "-" & Mid(cpr, 3, 2) & "-" & bytCent & bytCprYear
End If

End Function

Thanks!

[FrankAT]

Hi @Niels_Andersen,

take a look at the following calculated column:

Regards FrankAT

[Niels_Andersen]

Thank you @FrankAT  this works perfectly!
I accepted @mwegener's reply as the solution, because I wrote I was looking for an M syntax solution.

[mwegener · Accepted solution]

Hi @Niels_Andersen 

check this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA1NLKwNI2IiFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"social security number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"social security number", type text}}),
    #"Extract Day" = Table.AddColumn(#"Changed Type", "Day", each Number.FromText(Text.Start([social security number], 2)), type number),
    #"Extract Month" = Table.AddColumn(#"Extract Day", "Month", each Number.FromText(Text.Middle([social security number], 2, 2)), type number),
    #"Extract Year" = Table.AddColumn(#"Extract Month", "Year", each Number.FromText(Text.Middle([social security number], 4, 2)), type number),
    #"7th" = Table.AddColumn(#"Extract Year", "7th", each Number.FromText(Text.Middle([social security number], 6, 1)), type number),
    Century = Table.AddColumn(#"7th", "century", each if [7th] <= 3
then 1900
else if ([7th] = 4 or [7th] = 9) and [Year] <= 36
then 2000
else if ([7th] = 4 or [7th] = 9) and [Year] > 36
then 1900
else if ([7th] = 5 or [7th] = 😎 and [Year] <= 36
then 2000
else if ([7th] = 5 or [7th] = 😎 and [Year] >= 58
then 1800
else 0, type number),
    Birthdate = Table.AddColumn(Century, "Birthdate", each #date([century]+[Year],[Month],[Day]), type date)
in
    Birthdate

[Christyann7]

565-21-3304

[Niels_Andersen]

Thank you! This solved my problem.