DOCSEARCHbyAlgolia
Submit Docs

Power Query Reference

website·Indexed 32 days ago

https://powerquery.io/

Technical documentation and syntax reference for Power Query functions, including comprehensive guides for Table operations (transformation, construction, and membership), Number conversions, and data accessing functions.

Tokens
175.6K
Snippets
1.5K
Records
1.6K
Agent score
59%

What's inside Power Query

  1. Overview of the Power Query M formula language

    The Power Query M formula language is a functional, case-sensitive language similar to F#, optimized for building flexible data mashup queries. It is utilized across several Microsoft products, including Power BI Desktop, Excel, and Analysis Services.

  2. Overview of PQDriverless binary connectors

    PQDriverless is an open-source package (Apache-2.0) of pure M readers for binary file formats. It allows Power BI and Excel to decode binary files directly at query time using Binary.Range, BinaryFormat.*, and Binary.Decompress, removing the requirement for machine-level driver installations (ODBC, OLE DB, etc.).

    Because these are plain M source scripts, they work in Power BI Desktop, Excel, dataflows, and the Power BI Service. If the source file is hosted on SharePoint, OneDrive, or Blob storage, the need for an on-premises data gateway is also eliminated.

  3. Understand the PQDriverless function contract

    PQDriverless provides a set of open-source, pure M readers for binary file formats that do not require ODBC or OLE DB drivers. Every connector in the library follows a consistent function signature: it accepts a binary source and an optional record of options, returning a table.
    Reader.Function(
    source as binary,
    optional options as record
) as table

  4. Retrieve Planview Enterprise Architecture data using Troux.CustomFeed

    The Troux.CustomFeed function allows you to import data from a Planview Enterprise Architecture account into a table. It requires the account URL and a specific query string to filter the returned data.
    Troux.CustomFeed(
    url as text,
    query as text
) as table

  5. Retrieve Access database metadata

    You can extract metadata about the Access file, such as the version, format, and creation date, by using Value.Metadata on the result of the AccessReader.Database function. Available metadata keys include Access.Version, Access.Format, and Access.CreationDate.
    let
    Db = AccessReader.Database(File.Contents("C:\data\example.accdb"))
in
    Value.Metadata(Db)[Access.Version]

  6. Retrieve Planview Enterprise Architecture data using Troux.Feed

    The Troux.Feed function connects to a Planview Enterprise Architecture account and returns the account's data as a table. To use this function, you must provide the URL of your Planview Enterprise Architecture account as a text string.
    Troux.Feed(
    url as text
) as table

  7. Understand the output of SoftOneBI.Contents

    When calling SoftOneBI.Contents(), the resulting table contains metadata about the datalake objects. The output includes columns such as Name, ItemKind, ItemName, Data (which contains the actual table content), and IsLeaf.
    #table({"Name"}, {"ItemKind"}, {"ItemName"}, {"Data"}, {"IsLeaf"}, {{"Companies"}, {"Table"}, {"Table"}, {"Table"}, {"true"}})

  8. Retrieve table partitioning information using Table.PartitionValues

    Use Table.PartitionValues to get information about how a table is partitioned. The function returns a table where each column represents a partition column from the original table, and each row represents a specific partition within that table.
    Table.PartitionValues(
    table as table
) as table

  9. Retrieve LEAP data using LEAP.Contents

    The LEAP.Contents function returns a table containing relevant LEAP data. This table typically includes metadata about the available data folders and items, such as their names, keys, and types.
    LEAP.Contents()

  10. Read SQLite databases using Sqlite3.Database

    The Sqlite3.Database function is part of the PQDriverless library and allows Power Query to parse SQLite file formats (.sqlite, .db, .db3) directly from binary data. This eliminates the need for machine-level ODBC driver installations, making it compatible with locked-down desktops, Power BI Gateways, and the Power BI Service.

    It returns a navigation table containing the tables found within the SQLite database file.

    Sqlite3.Database(
    source as binary,
    optional options as record
) as table

  11. Example usage of Text.PadEnd for string padding

    The following examples demonstrate how to pad a string to a length of 10 characters using both the default space character and a custom character ('|').
    // Pad with default spaces to 10 characters
Text.PadEnd("Name", 10)
// Result: "Name      "

// Pad with a custom character "|" to 10 characters
Text.PadEnd("Name", 10, "|")
// Result: "Name||||||"

  12. Example: Create an open record type from a closed record type

    This example demonstrates how to transform a closed record type type [ A = number] into an open record type type [A = number, ...], which indicates that the record must have field A as a number but may contain other fields.
    // Input: Closed record type
Type.OpenRecord(type [A = number])

// Result: Open record type
// type [A = number, ...]