# Convert VBA function into Power Query - help needed

#### didijaba

##### Well-known Member
HI,
I'm trying to convert this VBA into Power Query function. I have tried but my knowledge is limited and I need help with Next loop and wrapping all If statements. Thanks in advance.
Code:
``````Public Function oibOk(oib As String) As Boolean
Dim a As Integer
Dim k As Integer
If 11 = Len(oib) Then
a = 10
For i = 1 To Len(oib) - 1 Step 1
o = Mid\$(oib, i, 1)
a = a + o
a = a Mod 10
If 0 = a Then
a = 10
End If
a = a * 2
a = a Mod 11

If IsNumeric(o) Then
sum = o + sum
End If
Next i
k = 11 - a
If 10 = k Then
k = 0
End If
If k = Mid\$(oib, i, 11) Then
oibOk = True
Else
oibOk = False
End If
Else
oibOk = False
End If
End Function``````

Last edited:

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Matt Allington

##### MrExcel MVP
It is impossible to help you with the information you have provided. I suggest you produce a sample workbook with sample source data and an example of what the end state is - including any rules for the conversion.

#### didijaba

##### Well-known Member
Hi, first of all thanks for taking interest in this problem. This is link to the demo file showing source and how result should look (VBA code is used to produce results) http://www.filedropper.com/iso7064mod1110 and to steps explained, just turn on show formulas is excel http://www.filedropper.com/stepsexplained.
This is based on ISO 7064 MOD 11,10.
OIB (that is name of this numeric string) has 11 digits of which the last ie. 11th digit check digit -
was obtained by calculation from the previous 10 digits after the international standard ISO
7064 (MOD 11, 10).

Last edited:

#### Matt Allington

##### MrExcel MVP
Hi

Ok, this is far to hard to understand without quite some investment of time and effort. I can tell you that this definitely can be done in Power Query, but unfortunatley it is not something I can invest time (1-2 hours) trying to solve for you for free. Maybe someone else has the time and can do it for you - I don't know.

If you already have a good understanding of what needs to be done, you can probably work it out yourself by simply doing 1 step at a time in the Power Query window. If that is not something that excites you, I offer commercial services to do work like this. You can contact me via private message or via my website if you are interested in that.

Remote Help & Support - Excelerator BI

#### didijaba

##### Well-known Member
Thanks Matt, your paying services will not be needed. I will go step by step, and build large formula in Power Query. What I was asking was is there a way o have loop done in Power Query (For i=1 to 10). Thanks for suggesting to upload files for better understanding of problem posted.

#### ImkeF

##### Well-known Member
Check out this blogpost: ISIN Validation (M Function) - Excel Evolution

Seems like this is very similar to your request. He didn't use a loop, but created a staging table instead with one row per number. This is one of the nice aspects of M in my eyes, that it makes your operations so visible.

If you like to debug this function in order to make it work for you, check out my article here that should help you with it: How to analyse M-functions step-by-step? – The BIccountant

However, if you are interested in how loops can be defined in M (you will need them for real recursion), this is the place to start: https://blog.crossjoin.co.uk/2014/0...replacements-of-words-in-text-in-power-query/

• didijaba

#### didijaba

##### Well-known Member
Thanks Imke, I will use links provided as guide for solving my problem. Thanks a lot #### billszysz

##### Active Member
Hi didijaba Try this code below if you want.
Code:
``````let
fxChecking = (txt as text) as logical =>
let
ToList = List.Transform(Text.ToList(txt), each Number.From(_)),
LastDigit = List.Last(ToList),
ListOfDigits = List.FirstN(ToList, 10),
Check = fxCheck( 1, ListOfDigits, 10, LastDigit)
in
Check,

fxCheck = (n as number, lst as list, r as number, ld as number) as logical  =>
let
One = Number.Mod(lst{n-1} + r,10),
Two = if One = 0 then 10 else One,
Three = Number.Mod(Two * 2, 11),
Final = if n = 10
then
if Three = 1 then 0 = ld else 11 - Three = ld
else
@fxCheck( n+1, lst, Three, ld)

in
Final,

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChType = Table.TransformColumnTypes(Source,{{"OIB", type text}}),
Ready = Table.AddColumn(ChType, "Result", each if Text.Length([OIB]) <> 11 then false else fxChecking([OIB]) )
in
Regards.

• citizenbh, ImkeF and didijaba

#### didijaba

##### Well-known Member
Hi billszysz,
this looks great . It is 3:15AM at my end so I can't do proper review, but this looks amazing . Thanks a lot, you'll have good karma in years to come #### didijaba

##### Well-known Member
Billszysz, I have looked at your code, and I can just say wow . There is so much to learn from your code. I just added part with error handling in final line to turn errors into false. Thanks a lot Billszysz, you made my day .
Code:
``````let
fxChecking = (txt as text) as logical =>
let
ToList = List.Transform(Text.ToList(txt), each Number.From(_)),
LastDigit = List.Last(ToList),
ListOfDigits = List.FirstN(ToList, 10),
Check = fxCheck( 1, ListOfDigits, 10, LastDigit)
in
Check,

fxCheck = (n as number, lst as list, r as number, ld as number) as logical  =>
let
One = Number.Mod(lst{n-1} + r,10),
Two = if One = 0 then 10 else One,
Three = Number.Mod(Two * 2, 11),
Final = if n = 10
then
if Three = 1 then 0 = ld else 11 - Three = ld
else
@fxCheck( n+1, lst, Three, ld)

in
Final,

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChType = Table.TransformColumnTypes(Source,{{"OIB", type text}}),
Ready = Table.AddColumn(ChType, "Result", each try if Text.Length([OIB]) <> 11 then false else fxChecking([OIB]) otherwise false)

in

• citizenbh and ImkeF