Number.ExcelBin2Dec in Power Query Language M

[Vijay_A_Verma]

Use Case - Excel offers many number conversion functions and BIN2DEC (Binary to Decimal) is one of them. But Power Query's M language doesn't offer this conversion function. Below are some examples when a binary number is given and Excel's BIN2DEC function is applied on that. 

Also note that Excel's BIN2DEC doesn't support conversion of negative binary numbers. If you try to convert negative binary number, then Excel's BIN2DEC will give error.

Solution -  We, first need to understand the background logic behind how to convert from binary to decimal. The first method is position method. 

Start with right most digit and assign a position starting with 0 to all digits from right to left. Then multiply that digit with 2 raised to the power of that position. Sum the results. This will give the decimal equivalent to the binary number.

Having understood the logic, let's make the formula through which we can generate the decimal equivalent of a binary number. From the above logic, it is clear that we first need to break the binary number into its constituent digits. Text.ToList is the function which generates a list having all individual characters as elements of a list. Let's assume that binary number is contained within a field name Binary Number,

Hence, if I take Text.ToList(Text.From(1100111)) then I get a list having elements {"1","1","0","0","1","1","1"}. Please note that Text.ToList operates on text not on numbers, hence I had to use Text.From to convert given number into text.

We can extract the position/index of these elements by List.Positions.

Hence, we now have List.Positions(Text.ToList(Text.From([Binary Number])) which will be a list {0,1,2,3,4,5,6}

If we use List.Revese on Text.ToList(Text.From([Binary Number])) then this gives me this gives me {"1","1","1","0","0","1","1"}. I am reversing the list as last digit need to be multipled by 2^0, second last by 2^1 and so on where we will use position list derived which is {0,1,2,3,4,5,6}.

Hence, what we have is following to build a Number.ExcelBin2Dec function.

= List.Sum(List.Transform(List.Positions(Text.ToList(Text.From([Binary Number]))), (i)=> Number.Power(2,i)*Number.From(List.Reverse(Text.ToList(Text.From([Binary Number]))){i})))

 The other method is doubling method. In this method,previous digit is taken and doubled and then added to current digit. Now, the resulting sum needs to be doubled and then added to current digit. We do this till all digits are exhausted from left to right. When we start with first digit, we need to understand that the previous digit is 0 as there is no previous digit. 

Now let' take the example of 1011. We go from left to right.

First left most digit is 1 and previous digit is 0 (this 0 is not given as previous digit but we need to take this previous digit as 0)

Hence Round 1 result = 0*2+1 = 1
Round 2 result = Round 1 result * 2+ Current digit = 1*2+0 = 2

Round 3 result = Round 2 result * 2+ Current digit = 2*2+1 = 5

Round 4 result = Round 3 result * 2+ Current digit = 5*2+1 = 11

Hence answer would be 11. 

This is a perfect case for List.Accumulate function. Hence, we have following formula for Number.ExcelBin2Dec conversion.

= List.Accumulate(Text.ToList(Text.From([Binary Number])), 0, (s,c)=> s*2 + Number.From(c))

 Let's see the working of this on a binary number 1011. 

Text.ToList(Text.From([Binary Number])) will give a list {"1","0","1","1"}. When we run List.Accumulate, the following steps will be executed to give a result of 11.

Both approaches can be seen here - - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVIrVAdMQhoEBkhCcBQRQJkQRhA3VYgiUB7NjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Binary Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Binary Number", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "List Formula", each List.Sum(List.Transform(List.Positions(Text.ToList(Text.From([Binary Number]))), (i)=> Number.Power(2,i)*Number.From(List.Reverse(Text.ToList(Text.From([Binary Number]))){i})))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "List.Accumulate Formula", each List.Accumulate(Text.ToList(Text.From([Binary Number])), 0, (s,c)=> s*2 + Number.From(c)))
in
    #"Added Custom2"