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 . 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.FunctionParameters, Type.FunctionReturn and a looooong list of type to text conversion.
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.
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 ➡Originally published at http://querypower.com on May 1, 2017.