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

100%

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

✓ Published0🌍 Public

Last edited Apr 25, 2018

Created on Apr 25, 2018

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.

AI-generated description

Similar vizzes

Loading thumbnail…

HEX2DEC one liner implementation in Power Query M

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.

HHugoberry

63% match

Loading thumbnail…

R Regex matching of mentions in twitts. with Power Query and R

This example demonstrates how to extract regex-matched mentions (prefixed with `#` or `@`) from Twitter text using R within a Power Query workflow. The visualization shows the original tweets alongside a new `mention` column containing the first matched term. The solution uses the `R.Execute` function to run an R script that applies `regmatches` and `regexpr` with the pattern `[#@]\w+` across the `text` column. The R script processes the dataset provided by Power Query, leveraging R's base string manipulation functions. The data originates from a CSV file loaded via `Csv.Document`, with the final result passed back to Power Query through the `[Name="temp"]` output.

HHugoberry

62% 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

62% match

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

59% match

Loading thumbnail…

PowerQuery List functions with unicode examples

This cheat sheet visually maps Power Query's list functions using geometric unicode symbols as both data and output markers. It organizes functions into categories like Generators, Selection, and Ordering, with each entry showing a call and its returned list. The examples highlight type-specific behaviors, such as `List.Zip` combining symbols into pairs, `List.Sort` deduplicating while ordering, and generator functions producing date, time, and numeric sequences. Unicode shapes make the transformations immediately legible, turning the snippet into a compact reference for list operations.

HHugoberry

59% match

Loading thumbnail…

Power Query incremental load example for querypower.com

This example demonstrates a Power Query incremental load setup for querypower.com, showing how a simple list source is transformed step by step. The code defines a query named `Query1` that generates a numeric range from 0 to 9, serving as the foundational data source. It uses the Power Query M language’s `let` and `in` syntax to structure the transformation pipeline, with `Source` assigned the output of `{0..9}`. The visualization highlights the initial stage of an incremental refresh pattern, where only new or changed rows would be processed in subsequent runs. The example focuses on the query definition itself, not on external libraries or rendering, as the M code contains no visual output or advanced APIs.

HHugoberry

59% match

Loading thumbnail…

Extracting Power Query queries

This example shows how to extract Power Query query definitions from an embedded Power BI tabular model by reading the model’s in-memory connection string. The PowerShell script first locates the dynamic port number for the local Analysis Services instance by scanning workspace files for `msmdsrv.port.txt`. It then uses the `Invoke-ASCmd` cmdlet to query the `$SYSTEM.TMSCHEMA_DATA_SOURCES` schema, revealing the connection string. The script decodes the base64-encoded `Mashup` portion of that string, writes it to a binary ZIP archive, and uses `System.IO.Compression.ZipFile` to extract `Section1.m`, which contains the readable Power Query formulas. This approach exposes the model’s underlying data-transformation logic without any direct UI interaction.

HHugoberry

58% 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

58% match