Categories
Experiment Getting-Started Power BI Uncategorized

Inspecting Function “subtypes” in Power Query

PowerQuery has metadata that you don’t normally see. For example, take the function Table.FromRecords

Create a new blank query, and set the value to a function’s name. When you reference a function without arguments or parenthesis, it displays documentation. ( It’s mostly the same as the online docs )

Where does this come from? A lot of it is generated by metadata on the function’s type itself.

Let’s start drilling down. Using the function Value.Type you can view the type’s definition

It doesn’t seem very useful at first. The data we want is from the metadata, the Ascribed types.

Metadata of the function type

The first level describes the function using specific field names that the UI looks for . The UI renders some Html from the [Documentation.LongDescription] value.

You can drill down to [Documentation.Examples] , which is a list of records.

Viewing Documenation.Examples

FunctionParameters Types

There can be data defined for the arguments themselves. Parameters that are type any may have more information in their metadata.

Parameters’s may have metadata
Sometimes the metadata, of a parameter’s metadata — has more metadata!

Your First Function types

How do you document your own functions? Here’s a medium example. The function declared further below starts with this definition:

Text.ReplacePartialMatches_impl = (
        source as text, mapping as table
    ) as text => ...

To create the type, you start your function type almost the exact same as the definition. Then wrap it inside a type function

Text.ReplacePartialMatches.Type = type function(
        source as text,
        mapping as table
    ) as text meta [ .. ]

Next you start adding records to the final type’s metadata. If you haven’t seen the meta operator, check out Ben’s series:

The part that comes after meta operator is a regular record

let
    Text.ReplacePartialMatches = Value.ReplaceType( Text.ReplacePartialMatches_impl, Text.ReplacePartialMatches.Type ),
    Text.ReplacePartialMatches.Type = type function(
        source as text,
        mapping as Table.Type
    ) as text meta [
        Documentation.Name = "Text.ReplacePartialMatches",
        Documentation.LongDescription = Text.Combine({
            "Test strings for partial text matches. Replace the entire cell/value with the new replacement text.",
            "",
            "Mapping table requires two columns: <code>[Partial]</code> and <code>[New Value]</code> "

        }, "<br>")
    ],

    Text.ReplacePartialMatches_impl = ( source as text, mapping as table ) as text =>
        // todo: performance, exit early on first replacement
        let
            mappingList = Table.ToRecords( mapping ),
            result = List.Accumulate(
                mappingList,
                source,
                (state, cur) =>
                    if Text.Contains( state, cur[Partial], Comparer.OrdinalIgnoreCase )
                    then cur[New Value] else state
            )
        in result

in
    Text.ReplacePartialMatches

Query For the Screenshot

let
    Custom1 = Value.Type( Value.Type( Table.FromRecords ) ),
    fn_typeMeta = Value.Metadata( Value.Type( Table.FromRecords ) ),
    fn_typeMeta_example = ( Value.Metadata( Value.Type( Table.FromRecords ) )[Documentation.Examples]){1},
    t_fnParams = Type.FunctionParameters( Value.Type( Table.FromRecords ) ),
    fn_metaType = Value.Metadata( Type.FunctionParameters( Value.Type( Table.FromRecords ) ) ),
    type_ofFuncType = Value.Type( Type.FunctionParameters( Value.Type( Table.FromRecords ) ) [missingField] ),
    type_param_ofFuncType = Value.Metadata( Type.FunctionParameters( Value.Type( Table.FromRecords ) )[missingField] ),
    required_ofFuncType = Type.FunctionRequiredParameters( Value.Type( Table.FromRecords ) ) ,
    type_ofRequiredType = Value.Type( Type.FunctionRequiredParameters( Value.Type( Table.FromRecords ) ) ),
    type_ofType_ofRequiredType = Value.Metadata( Value.Type( Type.FunctionRequiredParameters( Value.Type( Table.FromRecords ) ) ) )
    
in
    type_ofType_ofRequiredType

See More

Functions

Categories
Command Line Getting-Started PowerShell Pwsh7+ Quick Tips What's New

PowerShell : Prefixing lines with the Pipe operator |

There’s a lot of ways to use line continuations in Windows Powershell without backticks . Powershell added a new one, the | pipe operator. It’s cleaner to read, and makes it easier to insert, delete, or toggle comments on the console.

Now you can write:

# Powershell
ls | sort Length
| Select -First 10
| ft Name, Length

Instead of piping on line endings

# Windows Powershell
ls | sort Length |
Select -First 10 |
ft Name, Length

# or
ls | sort Length | Select -First 10 | ft Name, Length