Forum Discussion
BinaryFormat.Binary , BinaryFormat.Byte,BinaryFormat.ByteOrder,BinaryFormat.Group,BinaryFormat.Lengt
All the functions you refer don't do what you think/expect. For Power Query a Binary is a data Type (cf. https://learn.microsoft.com/en-us/power-query/data-types & https://bengribaudo.com/blog/2018/09/13/4617/power-query-m-primer-part9-types-logical-null-binary)
As an example, if you invoke function Folder.Contents:
= Folder.Contents("D:\abc\wxyz")you get something like:
where each Binary in [Content] must be "decoded" with the appropriate function (Excel.Workbook for the first 2, Csv.Document/Lines.FromBinary for the 3rd). But, there isn't always - in PQ functions' shared library - one for all kind of Binaries. Remember a few years ago where there wasn't a Pdf.Tables function (for the 4th file)...
Back to the functions you mentioned I suggest read https://learn.microsoft.com/en-us/powerquery-m/binary-functions
Dec2Bin & Bin2Dec with Power Query
In the attached workbook you'll find 2 custom functions (not mine) as there's currently no PQ shared function similar to the Excel ones:
- fxNumberBaseConversion (Initial https://community.fabric.microsoft.com/t5/Desktop/Number-to-Binary/m-p/235261; Revised https://community.fabric.microsoft.com/t5/Power-Query/Integer-Decimal-to-Binary-in-PQ/td-p/2128543)
(I added a Comment in the function to explain the difference) - fxBinToDec (https://community.fabric.microsoft.com/t5/Community-Blog/Number-ExcelBin2Dec-in-Power-Query-Language-M/ba-p/2447644)
and a TableResult query
- In the 'Source' sheet I highlighted a couple of things for your attention
- In the 'Result' sheet the [Excel_BinToDec_Error] highlights why PQ returns null values in [Excel_BinToDec] (= 120 errors)
(Didn't investigate why Excel BIN2DEC returns -386 for Base = 1001111110)
- fxNumberBaseConversion (Initial https://community.fabric.microsoft.com/t5/Desktop/Number-to-Binary/m-p/235261; Revised https://community.fabric.microsoft.com/t5/Power-Query/Integer-Decimal-to-Binary-in-PQ/td-p/2128543)
All the functions you refer don't do what you think/expect. For Power Query a Binary is a data Type (cf. https://learn.microsoft.com/en-us/power-query/data-types & https://bengribaudo.com/blog/2018/09/13/4617/power-query-m-primer-part9-types-logical-null-binary)
As an example, if you invoke function Folder.Contents:
= Folder.Contents("D:\abc\wxyz")
you get something like:
where each Binary in [Content] must be "decoded" with the appropriate function (Excel.Workbook for the first 2, Csv.Document/Lines.FromBinary for the 3rd). But, there isn't always - in PQ functions' shared library - one for all kind of Binaries. Remember a few years ago where there wasn't a Pdf.Tables function (for the 4th file)...
Back to the functions you mentioned I suggest read https://learn.microsoft.com/en-us/powerquery-m/binary-functions
Dec2Bin & Bin2Dec with Power Query
In the attached workbook you'll find 2 custom functions (not mine) as there's currently no PQ shared function similar to the Excel ones:
- fxNumberBaseConversion (Initial https://community.fabric.microsoft.com/t5/Desktop/Number-to-Binary/m-p/235261; Revised https://community.fabric.microsoft.com/t5/Power-Query/Integer-Decimal-to-Binary-in-PQ/td-p/2128543)
(I added a Comment in the function to explain the difference) - fxBinToDec (https://community.fabric.microsoft.com/t5/Community-Blog/Number-ExcelBin2Dec-in-Power-Query-Language-M/ba-p/2447644)
and a TableResult query
- In the 'Source' sheet I highlighted a couple of things for your attention
- In the 'Result' sheet the [Excel_BinToDec_Error] highlights why PQ returns null values in [Excel_BinToDec] (= 120 errors)
(Didn't investigate why Excel BIN2DEC returns -386 for Base = 1001111110)
If we have 10 bits the first bit means sign. Thus 1001111110 is negative number. To receive the value we invert other bits (will be 0110000001) and add 1 to it, finally it'll be 0110000010 or 386 decimal. Taking the sign result is -386.
Agreed. Just highlighted the difference with PQ output in case anupambit1797 would want both functions to behave the same in which case Vijay's function will need a couple of adjustments...
