Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Niels_Andersen
Frequent Visitor

Calculation birthday from social security number (danish)

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!

1 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
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

5 REPLIES 5
FrankAT
Community Champion
Community Champion

Hi @Niels_Andersen,

take a look at the following calculated column:

 

10-07-_2020_11-58-15.png

 

Regards FrankAT

 

 

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.

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
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


565-21-3304

Thank you! This solved my problem.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.