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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
I_Like_Pi
Resolver II
Resolver II

Help with a Social Insurance Number validity check function

I have built the following Custom function based on the rules for a valid Social Insurance Number in Canada.

Unfortunately it is producing an error "Token Comma expected", highlighting the in statement at the close of the function.

I can not see why. So far as I can tell I have my commas in the right spots.

I am aware the SIN nerds debate about adding the checksum to the odd part, I am pretty sure Pwer Q isn't erring on that. 

 

(SSNumber as text) =>
let
   O_Part = Number.FromText(Text.Start(SSNumber,1)) + 
         Number.FromText(Text.At(SSNumber,2)) +
         Number.FromText(Text.At(SSNumber,4)) +
         Number.FromText(Text.At(SSNumber,6) +
         Number.FromText(Text.At(SSNumber,8)),
   E_2 = Number.FromText(Text.At(SSNumber,1)) * 2,
   E_4 = Number.FromText(Text.At(SSNumber,3)) * 2,
   E_6 = Number.FromText(Text.At(SSNumber,5)) * 2,
   E_8 = Number.FromText(Text.At(SSNumber,7)) * 2,
   E_Part = 
      ( if Text.Length(Text.From(E_2)) = 1 then E_2 else Number.FromText(Text.Start(Text.From(E_2),1)) + Number.FromText(Text.End(Text.From(E_2),1))) +
      ( if Text.Length(Text.From(E_4)) = 1 then E_4 else Number.FromText(Text.Start(Text.From(E_4),1)) + Number.FromText(Text.End(Text.From(E_4),1))) +
      ( if Text.Length(Text.From(E_6)) = 1 then E_6 else Number.FromText(Text.Start(Text.From(E_6),1)) + Number.FromText(Text.End(Text.From(E_6),1))) +
      ( if Text.Length(Text.From(E_8)) = 1 then E_8 else Number.FromText(Text.Start(Text.From(E_8),1)) + Number.FromText(Text.End(Text.From(E_8),1))),
   ValidSSN = (if Number.Mod(O_Part + E_Part, 10) = 0 then true else false) 
in
^^ ERROR LOCATION
  ValidSSN

 

 

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

Hi @I_Like_Pi ,

 

Check this part for the O_Part variable:

Number.FromText(Text.At(SSNumber,6)

It's missing the end ")" 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
I_Like_Pi
Resolver II
Resolver II

Thank you both 🙂

As you were within seconds of each other I will give one the solution and the other a kudos.

 

Can't believe I missed that.

lbendlin
Super User
Super User

you are missing a closing bracket here

 

 Number.FromText(Text.At(SSNumber,6) +

 

camargos88
Community Champion
Community Champion

Hi @I_Like_Pi ,

 

Check this part for the O_Part variable:

Number.FromText(Text.At(SSNumber,6)

It's missing the end ")" 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors