HEX2DEC one liner implementation in Power Query M

100%

HEX2DEC one liner implementation in Power Query M

✓ Published0🌍 Public

Last edited Mar 2, 2017

Created on Mar 2, 2017

This example demonstrates a compact implementation of hexadecimal-to-decimal conversion in Power Query M, a formula language used in Microsoft Power BI and Excel. It shows how a single-line function can replace a multi-step conversion process. The code defines a custom function named `HEX2DEC` that takes a text string as input and applies the `Expression.Evaluate` API with the literal prefix “0x” to interpret the hexadecimal value. The function then returns the decimal result directly. The visualization highlights the elegance of using M’s expression evaluation for parsing, offering a practical alternative to built-in conversion functions.

AI-generated description

Similar vizzes

Loading thumbnail…

DEC2BIN or HEX2BIN in Power Query M

This example demonstrates a Power Query M function that converts a decimal number to its binary string representation, implementing the equivalent of Excel’s DEC2BIN and HEX2BIN operations. The function, `Number.DEC2BIN`, takes a numeric input and uses `List.Generate` to iteratively extract bits via `Number.BitwiseAnd` and `Number.BitwiseShiftRight`. Each iteration prepends the least significant bit to an accumulating string, while the loop continues until the remainder becomes zero. The code relies on M’s native `Number.ToText` for bit conversion and explicitly shows the bitwise arithmetic and list-generation logic, with no external libraries or data sources involved.

HHugoberry

77% match

Loading thumbnail…

Power Query HexViewer

The Power Query HexViewer displays the raw binary contents of a local text file as a structured hex dump, showing each byte’s address, offset, and hexadecimal value in a table. It reads the file `gitignore_global.txt` using Power Query’s `File.Contents` and `BinaryFormat.List`, then transforms the byte stream with `List.Transform` and `List.Positions` to compute address and offset columns. The final output uses the `#table` constructor to render the parsed data, with `Binary.ToText` formatting each byte as uppercase hex. The visualization relies solely on Power Query’s M language functions, not external JavaScript libraries.

HHugoberry

66% match

Loading thumbnail…

Converting a Power BI tabular connection string to Binary

This example demonstrates a Power Query (M) function that converts a Power BI tabular connection string into a binary format. It shows how to extract and decode the embedded Mashup component from the semicolon-delimited string. The function first splits the connection string using `Splitter.SplitTextByDelimiter`, isolates the last segment, and removes the "Mashup=" prefix. It then uses `Binary.FromText` to decode the remaining Base64-encoded text into binary data. The rendering relies entirely on the M language’s standard library, with no external libraries. This approach is useful for scenarios where a connection string’s embedded model needs to be accessed programmatically.

HHugoberry

64% match

Loading thumbnail…

PowerQuery hexview

The example visualizes a hexadecimal dump of a file’s raw bytes as a grid, where each cell represents one byte’s value in uppercase hex notation. The PowerQuery code reads the file `gitignore_global.txt` using `File.Contents` and `BinaryFormat.List`, then maps each byte to its relative and offset position within the file. Non-printable characters are replaced with dots, while printable ones display as hex pairs. The resulting table is expanded via `Table.ExpandRecordColumn`, producing a row per byte with columns for the byte value, its modulo-16 column, and the offset base—offering a compact, text-based visual breakdown of binary content without external charting libraries.

HHugoberry

64% match

Loading thumbnail…

CRC32 implementation in Power Query M mostly inpired by the article about CRC32 computation http://www.sunshine2k.de/articles/coding/crc/understanding_crc.html and Bit Hacks https://graphics.stanford.edu/~seander/bithacks.htm

This example demonstrates a complete CRC32 implementation written in Power Query M, showing how to compute cyclic redundancy checks entirely within a data-transformation language. It displays both the algorithm’s core logic and its practical output as a hexadecimal checksum. The code defines a reverse function for 8-bit and 32-bit values, precomputes a lookup table using a polynomial of 0x4C11DB7, and iterates over input characters with an accumulator function. It relies on Power Query’s `Number.BitwiseAnd`, `Number.BitwiseShiftLeft`, `Number.BitwiseXor`, and `List.Accumulate` APIs. The final result reverses the raw CRC, XORs with 0xFFFFFFFF, and formats it as uppercase hex via `Number.ToText`. The source is a gist by Hugoberry, drawing from external CRC tutorials and bit-hack references.

HHugoberry

64% match

Loading thumbnail…

Power Query regex example from http://www.thebiccountant.com/2018/04/25/regex-in-power-bi-and-power-query-in-excel-with-java-script/

This example demonstrates how to extract numeric patterns from a mixed-language string using regular expressions within Power Query. It defines a custom function that invokes JavaScript’s `RegExp` and `String.match` via the `Web.Page` HTML/JavaScript execution environment, passing a target string and a regex pattern. The specific call extracts all digit sequences from “hello012中国1235” and returns them as a list. This approach highlights Power Query’s ability to leverage external scripting for text processing, bridging M’s native functions with JavaScript’s regex engine for advanced pattern matching.

HHugoberry

63% match

Loading thumbnail…

Power Query embedded in an excel odc connection file

This example shows how a Power Query formula is embedded inside an Excel ODBC connection file, specifically within a Mashup.xml document. It demonstrates the structure of a single query item that generates a simple list of numbers from 1 to 20 using the `let` and `in` syntax. The code, written in Power Query’s M language, defines a source as a list range and then outputs that list. The file includes metadata such as client type (EXCEL), version, and culture settings, and the query is stored as a CDATA block within the XML, allowing for direct execution by Excel’s data mashup engine.

HHugoberry

61% match

Loading thumbnail…

UnZip in Power Query M

This example demonstrates a custom implementation of the UnZip algorithm entirely in Power Query M, enabling direct extraction of compressed ZIP file contents without external dependencies. It shows how binary data can be parsed and decompressed within Power BI’s data transformation environment. The code defines a binary format using `BinaryFormat.Record` to parse ZIP file headers, including signatures, compression flags, and sizes, then applies `BinaryFormat.Transform` with `Compression.Deflate` to decompress individual file entries. The result is a table of filenames and their uncompressed contents, derived from a gist by Hugoberry, showcasing low-level binary handling and list transformations in M.

HHugoberry

61% match