Visualizing the M library

To implement complex and less common data transformation requirements, it is often necessary to browse the M library to find a specific function or review the parameters of a specific function.

This short recipe provides a pre-built M query expression you can use to retrieve the M library into a table for analysis in Power BI Desktop. Additionally, an example is provided of visualizing and cross-filtering this table of functions on the Power BI report canvas.

Getting ready

To get ready for this recipe, do the following:

1. Open Power BI Desktop.
2. Click the Transform data icon in the ribbon of the Home tab.
3. Create a new Blank Query and call this query MLibrary.

How to Visualize M library

To implement this recipe, perform the following steps:

1. Enter the following M code in the Advanced Editor:

   let
       Source = Record.ToTable(#shared),
       Rename = Table.RenameColumns(Source, {{"Name", "Function"}}),
       Sort = Table.Sort(Rename, {{"Function", Order.Ascending}}),
       Dupe = Table.DuplicateColumn(Sort, "Function", "Function2"),
       Split = 
           Table.SplitColumn(
               Dupe, "Function2",
               Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),
               {"Group", "Detail"}
           ),
       MLibraryTable = 
           Table.TransformColumnTypes(
               Split, {{"Group", Text.Type}, {"Detail", Text.Type}}
           )
   in
       MLibraryTable
   

2. Click the OK button to close the Advanced Editor. The preview area should now look similar to that shown in Figure 2.41.

   

   Figure 2.41: Query Editor view of library table function

3. Click on Close and Apply from the Query Editor.
4. The 1,000+ rows from the M library are now loaded to the Data mode.
5. Create a visualization that uses the Function Groups column for filtering.

   

Figure 2.42: Report page of M standard library

How it works

The M expression leverages the #shared variable, which returns a record of the names and values currently in scope. The record is converted to a table value and then the Function column, originally Name in the context of the library, is split based on the period delimiter to allow for the Group column.

There's more...

M library details for every function are made available by entering the function without any parameters.

Figure 2.43: Library Function Details

See also

• Power Query function reference: http://bit.ly/3bLKJ1M