Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
27 | |
12 | |
12 | |
11 | |
9 |
User | Count |
---|---|
53 | |
28 | |
17 | |
14 | |
13 |