Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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.