Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
20 | |
20 | |
10 | |
10 | |
10 |