Turn IP Range into list of IP's

[ConorFitz]

Helllo,

I have been looking around but unable to find a solution to get a list of IP addresses out from a range,

eg 192.168.1.1 - 192.168.1.24

Is this possible to do in PowerBI?

Any help appreciated!

[v-juanli-msft · Accepted solution]

Hi @ConorFitz

If your IP adress has the same number before the last ".", just as your example, you could try my method.

Write code in Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ00jM0s9Az1DNU0FVA8IxMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], ".", 2), type text),
    #"Duplicated Column" = Table.DuplicateColumn(#"Inserted Text Before Delimiter", "Column1", "Column1 - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2", type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type1", {{"Column1 - Copy.1", each Text.AfterDelimiter(_, ".", 2), type text}}),
    #"Extracted Text After Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Column1 - Copy.2", each Text.AfterDelimiter(_, ".", 2), type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Text After Delimiter1",{{"Column1 - Copy.1", Int64.Type}, {"Column1 - Copy.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each {[#"Column1 - Copy.1"]..[#"Column1 - Copy.2"]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Custom", "Merged", each Text.Combine({[Text Before Delimiter], Text.From([Custom], "en-US")}, "."), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Text Before Delimiter", "Custom"})
in
    #"Removed Columns1"

          ->             

Best Regards

Maggie

[andrewfirth]

@v-juanli-msft 

Thanks a million for this! I had exactly the same problem, lots of IP address ranges (mask 24) that I needed the IP addresses for. 

I wonder how this can be edited for larger ranges that affect the values at different decimal intervals; i.e. mask 21;

10.2.128.0/21 contains every IP between 10.2.128.0-10.2.135.255 where the min & max of the final set of digits is 0 & 255.

[Anonymous]

RandomIPs = CONCATENATE("192.168.1.",RANDBETWEEN(1,24))