Query builder say no syntax error, but query reports error

[vilmarci]

I converted an Excel formula to PowerQuery (need to generate subnets in CIDR notation from IP address and network size).

I have 2 columns in my table: IP and Size.

This is the formula I ended up with:

Number.BitwiseAnd(
    (
        Text.Start(
            [IP],
            Text.PositionOf(Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 1)) - 1
        )
    ),
    (BinaryFormat.Decimal(Text.PositionOf(Number.BitwiseAnd("1", [Size]) & Number.BitwiseAnd("0", 32 - [Size]), 1, 8)))
)
    & "."
    & Number.BitwiseAnd(
        (
            Text.PositionOf(
                [IP],
                Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 1)
                ) + 1,
                Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 2)
                ) - Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 1)
                ) - 1
            )
        ),
        (
            BinaryFormat.Decimal(
                Text.PositionOf(Number.BitwiseAnd("1", [Size]) & Number.BitwiseAnd("0", 32 - [Size]), 9, 8)
            )
        )
    )
    & "."
    & Number.BitwiseAnd(
        (
            Text.PositionOf(
                [IP],
                Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 2)
                ) + 1,
                Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 3)
                ) - Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 2)
                ) - 1
            )
        ),
        (
            BinaryFormat.Decimal(
                Text.PositionOf(Number.BitwiseAnd("1", [Size]) & Number.BitwiseAnd("0", 32 - [Size]), 17, 8)
            )
        )
    )
    & "."
    & Number.BitwiseAnd(
        (
            Text.PositionOf(
                [IP],
                Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 3)
                ) + 1,
                Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP] & ".", ".", Character.FromNumber(160), 4)
                ) - Text.PositionOf(
                    Character.FromNumber(160), Replacer.ReplaceText([IP], ".", Character.FromNumber(160), 3)
                ) - 1
            )
        ),
        (
            BinaryFormat.Decimal(
                Text.PositionOf(Number.BitwiseAnd("1", [Size]) & Number.BitwiseAnd("0", 32 - [Size]), 25, 8)
            )
        )
    )
    & "/"
    & [Size]

When I add this as a custom column, I get "No syntax errors have been detected".

But, when I run the query, I get this in every row:

Expression.Error: 4 arguments were passed to a function which expects 3.
Details:
    Pattern=
    Arguments=[List]

Is there any way to know where is the issue exactly?

The original excel formula if this, if anyone interested, A2 is the IP address, B2 is network size.

=BITAND((LEFT(A2; FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);1))-1));(BIN2DEC(MID(REPT("1";B2)&REPT("0";32-B2);1;8))))&"."&BITAND((MID(A2;FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);1))+1;FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);2))-FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);1))-1));(BIN2DEC(MID(REPT("1";B2)&REPT("0";32-B2);9;8))))&"."&BITAND((MID(A2; FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);2))+1;FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);3))-FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);2))-1));  (BIN2DEC(MID(REPT("1";B2)&REPT("0";32-B2);17;8))))&"."&BITAND(  (MID(A2; FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);3))+1;FIND(CHAR(160);SUBSTITUTE(A2&".";".";CHAR(160);4))-FIND(CHAR(160);SUBSTITUTE(A2;".";CHAR(160);3))-1));  (BIN2DEC(MID(REPT("1";B2)&REPT("0";32-B2);25;8)))) & "/"& B2

Thanks,

vm

[HotChilli]

This just needs to be debugged.  Copy it into notepad as a backup then comment everything out and return something which you expect to work. 

If that works, put back a small-ish discrete section of code and return dummy values for the rest of it.

Repeat until it breaks and then focus your attention on the part that broke.