PowerBI Advance Editor Error - Token Eof expected

[maneeshsinha]

Hi,

I am getting an error message "Token Eof expected." while adding additional 'let' in an existing M function. Second screenshot is attached for reference.

The first 'let' section is working perfectly fine (first screenshot is attached for reference). Getting an error only when adding an additional second 'let' statement.

Please help. Thanks!!

let
Source = Excel.Workbook(File.Contents("C:\Users\SINHAM\OneDrive\MANEESH\DCS\1. BI Dashboard\PowerBI Dashboard\1. Latest-Version\1. FINAL\1. Dash\CMDB Baseline IP Subnet Ranges Expanded.xlsx"), null, true),
#"CMDB IP Address_Sheet" = Source{[Item="CMDB IP Address",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"CMDB IP Address_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Environment", type text}, {"Subnet", type text}, {"Suffix", Int64.Type}})
in
#"Changed Type"

let
Source = Excel.Workbook(File.Contents("C:\Users\SINHAM\OneDrive\MANEESH\DCS\1. BI Dashboard\PowerBI Dashboard\1. Latest-Version\1. FINAL\1. Dash\CMDB Baseline IP Subnet Ranges Expanded.xlsx"), null, true),
IsIPInSubnet = (IPAddress as text, Subnet as text) =>
let
SubnetAddress = Text.BeforeDelimiter(Subnet, "/"),
SubnetMask = Text.AfterDelimiter(Subnet, "/"),
SubnetBits = Text.ToNumber(SubnetMask),
IPBinary = Binary.From(Number.FromText(Text.Combine(List.Transform(Text.Split(IPAddress, "."), each Text.PadStart(_, 3, "0")), ""))),
SubnetBinary = Binary.From(Number.FromText(Text.Combine(List.Transform(Text.Split(SubnetAddress, "."), each Text.PadStart(_, 3, "0")), ""))),
SubnetMaskBinary = Number.ToText(Binary.ToNumber(List.FirstN( Binary.ToList(Number.ToBinary(2^32 - 2^ (32 - SubnetBits))), 4))),
Result = SubnetBinary = Binary.And(IPBinary, SubnetMaskBinary)
in
Result
in
IsIPInSubnet

[parry2k · Accepted solution]

@maneeshsinha you have to add 2nd let in a seperate query:

let
Source = Excel.Workbook(File.Contents("C:\Users\SINHAM\OneDrive\MANEESH\DCS\1. BI Dashboard\PowerBI Dashboard\1. Latest-Version\1. FINAL\1. Dash\CMDB Baseline IP Subnet Ranges Expanded.xlsx"), null, true),
IsIPInSubnet = (IPAddress as text, Subnet as text) =>
let
SubnetAddress = Text.BeforeDelimiter(Subnet, "/"),
SubnetMask = Text.AfterDelimiter(Subnet, "/"),
SubnetBits = Text.ToNumber(SubnetMask),
IPBinary = Binary.From(Number.FromText(Text.Combine(List.Transform(Text.Split(IPAddress, "."), each Text.PadStart(_, 3, "0")), ""))),
SubnetBinary = Binary.From(Number.FromText(Text.Combine(List.Transform(Text.Split(SubnetAddress, "."), each Text.PadStart(_, 3, "0")), ""))),
SubnetMaskBinary = Number.ToText(Binary.ToNumber(List.FirstN( Binary.ToList(Number.ToBinary(2^32 - 2^ (32 - SubnetBits))), 4))),
Result = SubnetBinary = Binary.And(IPBinary, SubnetMaskBinary)
in
Result
in
IsIPInSubnet

[parry2k · Accepted solution]

@maneeshsinha in left pane, right click -> new query -> Blank query and then click advanced editor and this is where you will copy the 2nd let.

[parry2k · Accepted solution]

@maneeshsinha in left pane, right click -> new query -> Blank query and then click advanced editor and this is where you will copy the 2nd let.

[maneeshsinha]

@parry2k Tried as suggested and got the new error 'Token ',' expected, screenshot below for reference. I reckon, I do have ',' in place. Am I missing somewhere?

[AndreDeLange · Accepted solution]

What does it show when you click on "Show error"

Seems it's unhappy with the highlighted part as it has been underlined:

[maneeshsinha]

Hi @AndreDeLange , thanks for the suggestion. Yes, you're right, I replaced '2^32' with 'Number.Power(2, 32)' and it's working perfectly fine now 🙂

Thank you so very much @AndreDeLange  & @parry2k for all your help. Much appreciated!!

Regards,

Maneesh.

[AndreDeLange]

Excellent, glad it helped

[parry2k · Accepted solution]

@maneeshsinha you have to add 2nd let in a seperate query:

let
Source = Excel.Workbook(File.Contents("C:\Users\SINHAM\OneDrive\MANEESH\DCS\1. BI Dashboard\PowerBI Dashboard\1. Latest-Version\1. FINAL\1. Dash\CMDB Baseline IP Subnet Ranges Expanded.xlsx"), null, true),
IsIPInSubnet = (IPAddress as text, Subnet as text) =>
let
SubnetAddress = Text.BeforeDelimiter(Subnet, "/"),
SubnetMask = Text.AfterDelimiter(Subnet, "/"),
SubnetBits = Text.ToNumber(SubnetMask),
IPBinary = Binary.From(Number.FromText(Text.Combine(List.Transform(Text.Split(IPAddress, "."), each Text.PadStart(_, 3, "0")), ""))),
SubnetBinary = Binary.From(Number.FromText(Text.Combine(List.Transform(Text.Split(SubnetAddress, "."), each Text.PadStart(_, 3, "0")), ""))),
SubnetMaskBinary = Number.ToText(Binary.ToNumber(List.FirstN( Binary.ToList(Number.ToBinary(2^32 - 2^ (32 - SubnetBits))), 4))),
Result = SubnetBinary = Binary.And(IPBinary, SubnetMaskBinary)
in
Result
in
IsIPInSubnet

[maneeshsinha]

Hi @parry2k ,

I truly appreciate your quick response. Thanks so much.

I'm pretty new to PowerBI, hence excuse me for asking a silly questions, please 🙂

How to create a seperate query to add a 2nd 'let'? 

Usually, I navigate to Advanced Editor by clicking on "Home" => "Transform Data" => "View" => "Advanced Editor". I can't see an option to create a seperate query.

Thank you so much again in advance for all your assistance!!

Regards,

Maneesh.