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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
DanielAravenaL
New Member

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

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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)

View solution in original post

1 REPLY 1
wdx223_Daniel
Super User
Super User

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)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.