Convert VBA function into Power Query - help needed

[didijaba]

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.

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

 

[Matt Allington]

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]

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).

 

[Matt Allington]

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]

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]

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]

Thanks Imke, I will use links provided as guide for solving my problem. Thanks a lot

 

[billszysz]

Hi didijaba
Try this code below if you want.

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]

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]

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 .

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