Convert VBA function into Power Query - help needed

didijaba

Well-known Member
Joined
Nov 26, 2006
Messages
511
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:

Some videos you may like

Excel Facts

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

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,216
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
Joined
Nov 26, 2006
Messages
511
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
Joined
Dec 18, 2014
Messages
1,216
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
Joined
Nov 26, 2006
Messages
511
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
Joined
Feb 1, 2015
Messages
544
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

Well-known Member
Joined
Nov 26, 2006
Messages
511
Thanks Imke, I will use links provided as guide for solving my problem. Thanks a lot :)
 

billszysz

Active Member
Joined
Feb 26, 2014
Messages
351
Office Version
365
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
    Ready
Regards.
 

didijaba

Well-known Member
Joined
Nov 26, 2006
Messages
511
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
Joined
Nov 26, 2006
Messages
511
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
    Ready
 

Watch MrExcel Video

Forum statistics

Threads
1,095,929
Messages
5,447,361
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top