March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
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)
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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.