Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hello,
I am looking for a way to convert the column ClientIP from TMG database into a legible IP address, the value is in the format:
C0A8019A-ffff-0000-0000-000000000000
the first 8 characters are the ip address in hexadecimal:
C0A8019A = 192.168.1.154
C0 = 192
A8 = 168
01 = 1
9A = 154
thanks
Solved! Go to Solution.
A Power Query solution:
let HexDigits ="0123456789ABCDEF", Source = #table(type table[ClientIP = text],{{"C0A8019A-ffff-0000-0000-000000000000"}}), First8Characters = Table.AddColumn(Source, "IP Address", each Text.Start([ClientIP], 8), type text), SplittedIntoPairs = Table.TransformColumns(First8Characters, {{"IP Address", Splitter.SplitTextByRepeatedLengths(2)}}), SplittedEachPair = Table.TransformColumns(SplittedIntoPairs,{{"IP Address", each List.Transform(_, Text.ToList)}}), HexToDec = Table.TransformColumns(SplittedEachPair,{{"IP Address", each List.Transform(_, each 16*Text.PositionOf(HexDigits,_{0})+Text.PositionOf(HexDigits,_{1}))}}), PartsCombined = Table.TransformColumns(HexToDec,{{"IP Address", each Text.Combine(List.Transform(_, Text.From),"."), type text}}) in PartsCombined
A Power Query solution:
let HexDigits ="0123456789ABCDEF", Source = #table(type table[ClientIP = text],{{"C0A8019A-ffff-0000-0000-000000000000"}}), First8Characters = Table.AddColumn(Source, "IP Address", each Text.Start([ClientIP], 8), type text), SplittedIntoPairs = Table.TransformColumns(First8Characters, {{"IP Address", Splitter.SplitTextByRepeatedLengths(2)}}), SplittedEachPair = Table.TransformColumns(SplittedIntoPairs,{{"IP Address", each List.Transform(_, Text.ToList)}}), HexToDec = Table.TransformColumns(SplittedEachPair,{{"IP Address", each List.Transform(_, each 16*Text.PositionOf(HexDigits,_{0})+Text.PositionOf(HexDigits,_{1}))}}), PartsCombined = Table.TransformColumns(HexToDec,{{"IP Address", each Text.Combine(List.Transform(_, Text.From),"."), type text}}) in PartsCombined
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.