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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.