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.
Documenation.ExamplesFunctionParameters Types
There can be data defined for the arguments themselves. Parameters that are type any may have more information in their 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.ReplacePartialMatchesQuery 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_ofRequiredTypeSee More
Functions
