Convert varbinary(4) to ip address

[manran]

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?

[manran]

wow, this worked great!  thanks for the help!

[AlexisOlson · Accepted solution]

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), ".")