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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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