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 )
![](https://ninmonkeys.com/blog/wp-content/uploads/2022/09/image-2.png)
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
![](https://ninmonkeys.com/blog/wp-content/uploads/2022/09/image-5.png)
It doesn’t seem very useful at first. The data we want is from the metadata, the Ascribed types.
![](https://ninmonkeys.com/blog/wp-content/uploads/2022/09/image-6.png)
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.
![](https://ninmonkeys.com/blog/wp-content/uploads/2022/09/image-7.png)
Documenation.Examples
Function Parameters Types
There can be data defined for the arguments themselves. Parameters that are type any
may have more information in their metadata.
![](https://ninmonkeys.com/blog/wp-content/uploads/2022/09/power-query-nested-sub-types-explains-array-2-Screenshot-2022-09-11-094910.png)
![](https://ninmonkeys.com/blog/wp-content/uploads/2022/09/power-query-nested-sub-types-explains-array-4-Screenshot-2022-09-11-094910.png)
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