Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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)