【实例】校验18位身份证号码的PowerQuery代码

网友评论

• 
  b15a2a40e6bb:对于电话号码有什么好的验证方法么，比如13500000000，13513513513

  PowerQuery:电话号码我目前只知道有11位，但号码构成规则我还不太清楚，比如开头三位是否要校验，后面8位数字有什么规则之类。所以之前校验手机号码只看它是否有11位，小于11或大于11都被认为是无效手机号。
• 
  b15a2a40e6bb:老师你好，如果需要有15位的老号码，能否增加个15位升18位的功能，如果是18位正确的返回y，15位的返回正确的18位省份证号

  PowerQuery:主体代码：
  let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU0sDAwMrQ0NjCwMLIwNTE0UorVgQkbmRoAJYCCSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [身份证号码 = _t]),
  标记18位的身份证号 = Table.AddColumn(Source , "标记18位的身份证号", each if Text.Length([身份证号码])=18 then "Y" else Text.Start([身份证号码],6)&"19"&Text.End([身份证号码],9)),
  #"Renamed Columns1" = Table.RenameColumns(标记18位的身份证号,{{"标记18位的身份证号", "去除空格"}}),//标记出18位的身份证号码，如果不是18位的，需要更新（Renew）
  
  计算余数 = Table.AddColumn(#"Renamed Columns1", "计算余数", each if [去除空格]="Y" then "Y" else Number.Mod((Number.FromText(Text.At([去除空格],0))*7+Number.FromText(Text.At([去除空格],1))*9
  
  +Number.FromText(Text.At([去除空格],2))*10+Number.FromText(Text.At([去除空格],3))*5+Number.FromText(Text.At([去除空格],4))*8+Number.FromText(Text.At([去除空格],5))*4+Number.FromText(Text.At([去除空格],6))*2
  
  +Number.FromText(Text.At([去除空格],7))*1+Number.FromText(Text.At([去除空格],8))*6+Number.FromText(Text.At([去除空格],9))*3+Number.FromText(Text.At([去除空格],10))*7+Number.FromText(Text.At([去除空格],11))*9
  
  +Number.FromText(Text.At([去除空格],12))*10+Number.FromText(Text.At([去除空格],13))*5+Number.FromText(Text.At([去除空格],14))*8+Number.FromText(Text.At([去除空格],15))*4+Number.FromText(Text.At([去除空格],16))*2),11)),
  
  校验码 = Table.AddColumn(计算余数, "校验码", each fn([计算余数])),
  
  更改的类型1 = Table.TransformColumnTypes(校验码,{{"校验码", type text}}),
  #"Added Custom" = Table.AddColumn(更改的类型1, "修正后的身份证", each if [校验码]="Y" then "Y" else [去除空格]&[校验码]),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"去除空格", "计算余数", "校验码"})
  in
  #"Removed Columns"
  
  fn函数代码：
  let
  Source = (input) =>
  let
  values = {
  {"Y", "Y"},
  {0, 1},
  {1, 0},
  {2, "X"},
  {3, 9},
  {4, 8},
  {5, 7},
  {6, 6},
  {7, 5},
  {8, 4},
  {9, 3},
  {10, 2},
  {input, "Undefined"}
  },
  Result = List.First(List.Select(values, each _{0}=input)){1}
  in
  Result
  in
  Source