When the trace files shows duplicate Web.Content which might be triggered by some operations down the Power Query pipeline, it is a good idea to Buffer the Web.Contents first

100%

When the trace files shows duplicate Web.Content which might be triggered by some operations down the Power Query pipeline, it is a good idea to Buffer the Web.Contents first

✓ Published0🌍 Public

Last edited Mar 2, 2017

Created on Mar 2, 2017

This example demonstrates a defensive Power Query pattern for handling duplicate `Web.Contents` calls in trace files, which can arise from downstream pipeline operations. The code defines a custom function, `Web.BufferedContents.m`, that buffers the binary response using `Binary.Buffer` and captures metadata via `Value.Metadata` to inspect the HTTP status. It explicitly handles 404 responses by returning `null` instead of an error, using the `ManualStatusHandling={404}` option to prevent automatic error propagation. The function wraps `Web.Contents` with buffering to improve performance when the same URL is requested multiple times, while also ensuring status codes are checked. This approach is particularly useful in ETL scenarios where repeated web requests can be triggered by later query steps.

AI-generated description

Similar vizzes

Loading thumbnail…

[Power Query] Experimenting with thunks and trampolines

This example demonstrates a manual implementation of tail-call optimization using thunks and a trampoline, written entirely in Power Query. It shows how recursive functions can be restructured to avoid stack overflow by wrapping each recursive call in a thunk—a zero-argument function—and then driving the computation forward with a trampoline that repeatedly invokes the current thunk until a non-function value is produced. The code defines two custom functions: `make_thunk.pq`, which uses `Function.Invoke` to delay a function call, and `trampoline.pq`, which uses `List.Generate` with type checking via `Type.Is` and `Value.Type` to iterate until the result is no longer a function. The example highlights a purely functional, dataflow-driven approach to control flow, with no external libraries or visual rendering, relying solely on Power Query’s standard library.

HHugoberry

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

50% match

Loading thumbnail…

Power Query attempt at decompiling a function call

This example shows a Power Query (M) custom function that decompiles a `List.Skip` call into its component parts—the list, the offset, and an optional count. It demonstrates how M’s standard library functions `List.Skip` and `List.FirstN` are composed to recreate the logic of a function call, with `skippedInput` holding the intermediate result after skipping a given number of elements. The code reveals the underlying structure of the operation by explicitly handling a null count fallback, which would otherwise be implicit in the original function. This visualization highlights the metaprogramming aspect of Power Query, where code itself is treated as data for inspection.

HHugoberry

50% match

Loading thumbnail…

Power Query navigate the documentation of #shared functions. [M code]

This example visualizes the complete set of functions exposed by Power Query’s `#shared` namespace, organized into a navigable table. It shows each function’s name, module, category, example code, description, and signature. The M code uses `Record.ToTable` and `Table.SelectRow` to filter functions, then leverages `Value.Metadata` to extract documentation fields like `Documentation.Category` and `Documentation.Examples`. It also parses module names via `Text.Split` and builds signatures dynamically. The result is a self-documenting inventory, enabling users to browse and understand the entire Power Query function library directly from the environment.

HHugoberry

48% match

Loading thumbnail…

Replicating R unz function in Power Query. Based on http://sql10.blogspot.co.uk/2016/06/reading-zip-files-in-powerquery-m.html

This example demonstrates how to read a specific file from a ZIP archive directly in Power Query using a custom M function. It shows the binary-parsing logic that replicates R’s `unz` utility by manually decoding the ZIP container format. The code defines binary readers via `BinaryFormat` components, including `UnsignedInteger32` and `UnsignedInteger16` for headers, and uses `Binary.Decompress` with `Compression.Deflate` to inflate the stored file data. A `BinaryFormat.List` iterator scans the archive for the `PKZIP` signature (`0x04034B50`) to locate and extract the requested file by name. The function returns the uncompressed binary content for the matching entry.

HHugoberry

48% match

Loading thumbnail…

Gist ba39cf5b6349b2131c577e2e046e8654

This example demonstrates a data-driven approach to XML serialization within the Power Query language, showing how database queries can be wrapped in a custom function. The code defines a let expression that calls the `Value.NativeQuery` function, targeting a SQL Server database named "QueryPower" and executing a stored procedure called `usp_InsertDemoXML` with a binary parameter. The procedure receives an XML payload stored in a variable named `dummy2XML`, which is passed as the `xmlBin` argument. This illustrates a pattern for embedding XML data into SQL Server stored procedures through Power Query, highlighting how native database queries can be integrated into data transformation workflows. The example focuses on the parameter-passing mechanism rather than visual rendering.

HHugoberry

48% match

Loading thumbnail…

Gist 8abd0d0a72b91b4d72ce79ac9d8ab6c4

This example demonstrates how Power Query’s `BinaryFormat.List` function reads binary data as a sequence of items, returning a list. It shows three distinct modes: reading until the data ends, reading a fixed number of items, and using a condition function to stop based on item values. The code constructs sample binary data with `#binary`, then applies `BinaryFormat.List` with `BinaryFormat.Byte` as the item format. The condition variant passes a lambda `(x) => x < 2` that continues reading while true, including the first failing item. The output lists (e.g., `{1, 2, 3}`) directly display the parsed bytes.

HHugoberry

47% match

Loading thumbnail…

Adding a delay between request for computing a Mandelbrot Set calculation and a fetch the results from blob storage

This example demonstrates how to introduce a deliberate ten-second pause between scheduling a Mandelbrot Set calculation and retrieving the result from blob storage. It uses Power Query’s `Function.InvokeAfter` to delay the execution of `MandelbrotBlobFetch`, which asynchronously fetches the computed image from a blob container. The `MandelbrotSchedule` function determines the target folder path, while the delay ensures the computation has finished before the data is pulled. This pattern is useful for coordinating long-running background tasks with subsequent data retrieval in a data-refresh pipeline.

HHugoberry

47% match