Closures, metadata and cascading parameters

If you’re interested in cascading parameters and Power BI custom connectors is an option for your project, you should give a try to Navigation Tables. Which soon enough could make their way into M standard library. If this is not an option, you might find your solution in the next few scrips tangled between functional programming concepts. You will also learn about:

  1. Where to set the allowed values for function parameters
  2. How to build a function inception with higher order functions
  3. How to access a variable outside its scope
  4. And finally get to a result which mimics cascading selectionsRead More »

Navigating over 600+ M funcitons

Whenever I’m faced with a data mashup problem in Power BI, I try to check if it can be resolved with a standard M functions exposed by the intrinsic variable #shared. Navigating this data structure when you don’t know what you’re looking for seems to be tedious task. Up until recently my function discovery workflow included to turn the output of #shared into a table and then search for keywords in the name of the functions. Hoping that the developers called the functions accordingly. However, after discovering where the documentation for the functions is stored I’ve come up with more flexible ways of navigating the 600+ definitions.

Function documentation

A while ago I’ve stumbled across a wonderful GitHub repository — Power BI Desktop Query Extensions written by Taylor Clark. This is one of those hidden treasures that is filled with golden nuggets. I recommend it to anyone interested in taking their Power Query skills to the next level. Although the repository is actually a single file with 545 LOC, in those lines you’ll see some of the best examples of text, list and table manipulation. My favorite finds are those on testing and documenting M functions.

Apparently the documentation that you see while browsing #shared is stored as a metadata record on the type of the functions.


In order to view it you need to first check the type of the function and then retrieve the metadata. For example:

= Value.Metadata(Value.Type(List.Positions))


Something that looks like a record in the query view, can easily be transformed into a table. And there are so many ways you can render the information stored as a table.

Function signature

Before proceeding any further there is still one piece of the puzzle missing —the signature of the functions. I couldn’t find it neither in the documentation metadata, nor via other standard functions. So I had to roll out a custom query that generates a text like:

"function (list as list) as list"

It uses a combination of Type.FunctionParametersType.FunctionReturn and a looooong list of type to text conversion.

(placeholder as function)=>
//Serialize type to text
TypeAsText = (value as any) =>
prefix = if Type.IsNullable(value) then "nullable " else ""
if Type.Is(value, type binary) then "binary" else
if Type.Is(value, type date) then "date" else
if Type.Is(value, type datetime) then "datetime" else
if Type.Is(value, type datetimezone) then "datetimezone" else
if Type.Is(value, type duration) then "duration" else
if Type.Is(value, type function) then "function" else
if Type.Is(value, type list) then "list" else
if Type.Is(value, type logical) then "logical" else
if Type.Is(value, type none) then "none" else
if Type.Is(value, type null) then "null" else
if Type.Is(value, type number) then "number" else
if Type.Is(value, type record) then "record" else
if Type.Is(value, type table) then "table" else
if Type.Is(value, type text) then "text" else
if Type.Is(value, type time) then "time" else
if Type.Is(value, type type) then "type" else
if Type.Is(value, type any) then "any"
else error "unknown"),
//if parameter is Optional set prefix
OptionalPrefix = (_)=>if Type.IsNullable(_) then "optional " else "",
//get list of function parameters
parameters = Type.FunctionParameters(Value.Type(placeholder)),
//create a text list of parameters and associate types "[optional] paramname as type"
parametersWithTypes = List.Accumulate(Record.FieldNames(parameters),{},
cur&" as "&TypeAsText(Record.Field(parameters,cur))})
//merge parameter list and prefix with "function (" and suffix with function return type
"function ("&
Text.Combine(parametersWithTypes,", ")&
") as "&
view raw Signature.cs hosted with ❤ by GitHub

Stiching everything together

I’ve started from #shared record, transforming it to table, then filtering only on function definitions and then added columns one by one for Category, Description, function Examples and Signature. After some string manipulation to parse the Module names I ended up with the query below.

sharedTab = Record.ToTable(#shared),
//select only functions from #shared
functions = Table.SelectRows(sharedTab, each Type.Is(Value.Type([Value]),type function)),
//parse Module from function name
modules = Table.AddColumn(functions, "Module", each Text.Split([Name], "."){0}, type text),
functionNames = Table.AddColumn(modules, "FunctionName", each List.Last(Text.Split([Name], ".")), type text),
//get category from documentation
categories = Table.AddColumn(functionNames, "Category", each try Value.Metadata(Value.Type(Record.Field(#shared,[Name])))[Documentation.Category] otherwise ""),
//parse only the first code example from documentation
examples = Table.AddColumn(categories, "Examples", each
let eg = Value.Metadata(Value.Type(Record.Field(#shared,[Name])))[Documentation.Examples]?
in if Type.Is(Value.Type(eg),type record) then eg[Code] else eg{0}?[Code]?),
//get the short description from the documentation
descriptions = Table.AddColumn(examples, "Description", each Value.Metadata(Value.Type(Record.Field(#shared,[Name])))[Documentation.Description]?),
//parse subcategories
subcategories = Table.AddColumn(descriptions, "DotCategory", each List.Last(Text.Split([Category],"."))),
//adding the signature of the functions
out = Table.AddColumn(subcategories, "Signature", each Signature(Record.Field(#shared,[Name])))
view raw NavigateShared.cs hosted with ❤ by GitHub

I was planning to represent this data in a tile chart. Similar to a periodic table. However after experiencing performance problems while rendering 600 tiles with Infographic Designer 1.5.2, I gave up on the whole idea and opted to visualize all of this information in a treemap.

I’ve added a dummy column([Uno]) with value 1 and used as a Value field in the treemap chart. Then I’ve added column [Module] to the Group field and the [FunctionName] in Details section. All of the remaining columns: [Description], [Signature] and [Examples], I’ve added to the Tooltips section of the chart. To control the treemap, I’ve used a matrix which acts as a filter on [Category] column.


Click to view the embedded version of the report ➡