Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |