Forum Discussion

manran's avatar
manran
New Member
2 years ago
Solved

Convert varbinary(4) to ip address

I have a column in a sql database with a varbinary(4) datatype.  I would like to display that column as ip addresses since that's what they represent.  In sql management studio the values appear as a hexadecimal value like: 0x7f000001.  However, power bi doesn't show the column in the data section.

 

I tried adding a new column using Binary.ToText([columnName]), but I ended up with values that didn't make sense to me.  Values like:  CgTHAA== 

 

Can someone help me understand what power bi is showing me, or point me in a better direction?

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    I think Binary.ToList is your friend here.

    Sample query 1:

     

    let
    bin = Binary.From("7f000001", BinaryEncoding.Hex),
    bin_2_list = Binary.ToList(bin),
    num_2_text = List.Transform(bin_2_list, Number.ToText),
    list_2_ip = Text.Combine(num_2_text, ".")
in
    list_2_ip

     

     

    Sample query 2:

     

    let
    Source = Table.FromColumns({{"798f7a55","849c8574","8fa99093","9ab69bb2","a5c3a6d1","b0d0b1f0","bbddbd0f","c6eac82e"}}, {"IP"}),
    BinaryFormat = Table.TransformColumns(Source,{{"IP", each Binary.FromText(_, BinaryEncoding.Hex), type binary}}),
    IPs = Table.TransformColumns(BinaryFormat, {{"IP", each Text.Combine(List.Transform(Binary.ToList(_), Number.ToText), "."), type text}})
in
    IPs

     

    You can add a new custom column by pasting this into the box:

    Text.Combine(List.Transform(Binary.ToList([IP]), Number.ToText), ".")

     

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    I think Binary.ToList is your friend here.

    Sample query 1:

     

    let
    bin = Binary.From("7f000001", BinaryEncoding.Hex),
    bin_2_list = Binary.ToList(bin),
    num_2_text = List.Transform(bin_2_list, Number.ToText),
    list_2_ip = Text.Combine(num_2_text, ".")
in
    list_2_ip

     

     

    Sample query 2:

     

    let
    Source = Table.FromColumns({{"798f7a55","849c8574","8fa99093","9ab69bb2","a5c3a6d1","b0d0b1f0","bbddbd0f","c6eac82e"}}, {"IP"}),
    BinaryFormat = Table.TransformColumns(Source,{{"IP", each Binary.FromText(_, BinaryEncoding.Hex), type binary}}),
    IPs = Table.TransformColumns(BinaryFormat, {{"IP", each Text.Combine(List.Transform(Binary.ToList(_), Number.ToText), "."), type text}})
in
    IPs

     

    You can add a new custom column by pasting this into the box:

    Text.Combine(List.Transform(Binary.ToList([IP]), Number.ToText), ".")