Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
vilmarci
Frequent Visitor

Query builder say no syntax error, but query reports error

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

 

1 REPLY 1
HotChilli
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.