Forum Discussion

DanielAravenaL's avatar
DanielAravenaL
New Member
3 years ago
Solved

SQL function to M

Hi, I'm new with Power Query language, and I'm stuck with a SQL function. I cannot convert it

 

 

 

ALTER FUNCTION [dbo].[funcion_CalculaDigitoRut]
(
    Rut int        
) 
RETURNS char(2)
AS
BEGIN        DECLARE @digito int
       DECLARE @contador int
       DECLARE @multiplo int
       DECLARE @acumulador int
       DECLARE ret char
       set @contador = 2
       set @acumulador = 0
       while (@rut <> 0)
       begin
          set @multiplo = (@Rut % 10) * @contador
          set @acumulador = @acumulador + @multiplo 
          set Rut = Rut / 10 
          set @contador = @contador + 1 
          if (@contador = 😎 
             set @contador = 2 
       end
       set @digito = 11 - (@acumulador % 11)        if (@digito = 10)
          return ('-K')
       if (@digito = 11)
          return ('-0')
       return ('-' + convert(varchar(1),@digito)) 
END

 

 

Any idea how to convert it? 

 

Is a validation formula, that returns a digit between 0 and 9 and a letter "K" in some cases. In Chile you have an ID number with a verification one. Example ID 123456789-2 where 2 (the last one) is a verification digit. I'm trying to write it on powerquery but I failed. I know that integer type could be "number" or try an "if then else" but recursive steps like While is new to me. 

 

Also I have it for VBA:

 

 

    Public Function RutDigito(ByVal Rut As Long) As String
    Dim Digito      As Integer
    Dim Contador    As Integer
    Dim Multiplo    As Integer
    Dim Acumulador  As Integer
    Contador = 2
    Acumulador = 0
    While Rut <> 0
      Multiplo = (Rut Mod 10) * Contador
      Acumulador = Acumulador + Multiplo
      Rut = Rut \ 10
      Contador = Contador + 1
      If Contador > 7 Then
        Contador = 2
      End If
    Wend
    Digito = 11 - (Acumulador Mod 11)
    RutDigito = CStr(Digito)
    If Digito = 10 Then RutDigito = "K"
    If Digito = 11 Then RutDigito = "0"
  End Function

 

 

 

Using formulas like Number.Mod(Rut,10) or Value.Divide(Rut,10) but again.. the recursive part... 

 

 

 

(Rut as number, Digito as number, Multiplo as number) =>

let
    Contador = 2,
    Acumulador = 0,

Source = 

    if Rut <> 0 then 
        Multiplo = Number.Mod(Rut,10) * Contador and
        Acumulador = Acumulador + Multiplo and
        Rut = Value.Divide(Rut,10) and
        Contador = Contador + 1
    if Contador > 7 Then
       Contador = 2
    else @fRutDV

    Digito = 11 - Number.Mod(Acumulador,11)
    RutDigito = CStr(Digito)
    If Digito = 10 Then RutDigito = "K" else
    If Digito = 11 Then RutDigito = "0"

in
    source

 

 

  • fRutDV=(Rut as number,optional Contador as number,optional Acumulador as number) =>
            let
                xContador =Contador??2,
                xAcumulador =Acumulador??0,
                Digito= 11-Number.Mod(xAcumulador,11)
            in
                if Rut = 0 then
                    if Digito=10 then "K"
                    else if Digito=11 then "0"
                    else Text.From(Digito)
                else @fRutDV(Number.Round(Rut/10),if xContador>6 then 2 else xContador+1,xAcumulador + Number.Mod(Rut,10) * xContador)

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    fRutDV=(Rut as number,optional Contador as number,optional Acumulador as number) =>
            let
                xContador =Contador??2,
                xAcumulador =Acumulador??0,
                Digito= 11-Number.Mod(xAcumulador,11)
            in
                if Rut = 0 then
                    if Digito=10 then "K"
                    else if Digito=11 then "0"
                    else Text.From(Digito)
                else @fRutDV(Number.Round(Rut/10),if xContador>6 then 2 else xContador+1,xAcumulador + Number.Mod(Rut,10) * xContador)