The Great Function Project – Part 3

Lord of the Rings. Mad Max. Star Wars. Indiana Jones… The Great Function Project.

Another great trilogy comes to an end… well at least until the next one comes out.

If you haven’t “seen” part 1 & part 2, go back and check those out in sequence. We’re two-thirds of the way through a co-development project to build a complete reference of every DAX, Excel & M function available to you. In part 3 today, we’re finishing it out by adding the NKOTB into the mix – M.

Yeah, M has its challenges when you’re starting out with it… Search Engine utilization being just one of them. We’ll resolve that one today so that you’ll never need to search for an M function again.

Lack of Intellisense when writing M is another major one. Just like DAX and Excel, there is an expected syntax to M, but Power BI is not going to help you out with this – yet. We’re also going to improve that situation too by getting you definitions and examples of many of the functions.

Sounds good? Let’s go.
So click Edit Queries to enter the Power Query Editor, and we’ll get started.

The M Formula Bar is disabled by default, so make sure you have it enabled. If you don’t see it go to the View menu, then check the box on the left that says Formula Bar.

Go to New Source –> Blank Query

Not all M is going to be this easy, but this one will be a good confidence builder .  Go to the Formula bar and tweet write:

#shared

Yep, that’s it. One case-sensitive word gets you the complete library of M Functions – and some extras we’re not interested in right now.

A list of names isn’t enough you say? Well, aren’t you getting a little spoiled now? OK, OK. We can do this, but to get the rest of the metadata that I know you want, that will mean writing a few custom M columns and doing a little cleanup. It’s about a 10 step process, so for the sake of time, we’ll take a shortcut and you can reverse engineer the details of this query once we’re done (spoiler: Value.Metadata is the magic function).

Go to Home –> Advanced Editor

Then copy the below code over whatever you have appearing in the Advanced Editor window then hit OK.

let
Source = #shared,
#”Converted List to Table” = Record.ToTable(Source),
#”Added Type Column” = Table.AddColumn(#”Converted List to Table”, “ValueType”, each Value.Type([Value])),
#”Is this row a function?” = Table.AddColumn(#”Added Type Column”, “Is Function?”, each Type.Is([ValueType], type function)),
#”METADATA!” = Table.AddColumn(#”Is this row a function?”, “GoodStuff”, each Value.Metadata([ValueType])),
#”Show me the Metadata” = Table.ExpandRecordColumn(#”METADATA!”, “GoodStuff”, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}),
#”If there is more than one example take the first one” = Table.AddColumn(#”Show me the Metadata”, “Examples”, each try if Type.Is(Value.Type([Documentation.Examples]), type record) then [Documentation.Examples] else List.First([Documentation.Examples]) otherwise null),
#”Show me the examples” = Table.ExpandRecordColumn(#”If there is more than one example take the first one”, “Examples”, {“Description”, “Code”, “Result”}, {“Description”, “Code”, “Result”}),
#”Renamed Columns” = Table.RenameColumns(#”Show me the examples”,{{“Name”, “Function name”}, {“Code”, “Example Code”}, {“Result”, “Example Output”}, {“Documentation.Description”, “Short Description”}, {“Documentation.LongDescription”, “Long Description”}, {“Documentation.Name”, “Documentation Name”}, {“Documentation.Category”, “Category”}, {“Documentation.DisplayName”, “Documentation DisplayName”}, {“Documentation.Caption”, “Documentation Caption”}, {“Description”, “Example Description”}}),
#”Filter out non-functions” = Table.SelectRows(#”Renamed Columns”, each ([#”Is Function?”] = true)),
#”Removed Columns” = Table.RemoveColumns(#”Filter out non-functions”,{“Value”, “ValueType”, “Documentation.Examples”, “Is Function?”}),
#”Added Alt Category” = Table.AddColumn(#”Removed Columns”, “Alt Category”, each Text.BeforeDelimiter([Function name], “.”), type text),
#”Added Subcategory” = Table.AddColumn(#”Added Alt Category”, “Subcategory”, each Text.AfterDelimiter([Function name], “.”), type text),
#”Replaced Value” = Table.ReplaceValue(#”Added Subcategory”,”<code>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,”</code>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value2″ = Table.ReplaceValue(#”Replaced Value1″,”<ul>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value3″ = Table.ReplaceValue(#”Replaced Value2″,”<li>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value4″ = Table.ReplaceValue(#”Replaced Value3″,”</ul>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value5″ = Table.ReplaceValue(#”Replaced Value4″,”</li>”,””,Replacer.ReplaceText,{“Long Description”})
in
#”Replaced Value5″

And now you have a complete listing of 674 M functions along with a decent amount of the associated metadata! There are a total of 12 M functions being used in this query (highlighted yellow) and you now have the resource to take a closer look at what they do.

Bringing It All Together (Optional Exercise in Futility)

We’ve already created a lookup table to cross-reference DAX & Excel in Part 1. There’s no reason why we can’t add M to that lookup table, even though there is zero overlap.

You’re probably going to run into errors at this step or on the refresh of the Function Lookup Table. The DAX query from part 1 is going to be the culprit. You’ll need to re-run DAX Studio and update the DAX query Source step with both the new local host and new catalog_name fields following the steps from part one here or here.

To update your Function Lookup Table with the M functions.

1 – Select the Function Lookup Query you created in Part 2
2 – Click the gear icon on step 2 of the query ‘Appended Query’
3 – Change the default Append Query behavior from appending ‘Two tables’ to ‘Three or More tables.’
4 – Select the M table
5 – Click Add to now combine DAX, Excel & M

And we’re done. Hit close and apply and proceed to layout your complete Power BI & Excel reference in any way that works for you!

Let’s recap what WE covered in the Great Function Project:

We queried the metadata of a PBIX file (twice) even writing a little T-SQL along the way.

We used DAX Studio.

We connected to SQL Server Analysis Services.

We built a web scraping application.

We performed some ETL – indirectly writing M in the process.

We created a dynamic lookup table driven my 3 separate fact tables.

We have written M and we’ve also “borrowed” some, both of which are fine.

….and you have done this using a free application with no traditional data sources in (hopefully) less than 30 minutes total!

As much as this project was building up to the end product, we sure did get some reps in a lot of different techniques along the way! If any of this is new to you, I hope it’s demystified a few things for you.

Please download the .pbix for your reference here

I’d also LOVE to hear how you put your workbook to use and would love to see what you’ve put together. Feel free to share it with me mherring@p3adaptive.com.

Did you find this article easier to understand than the average “tech” article?

We like to think that is no accident.  We’re different.  First of a new breed – the kind who can speak tech, biz, and human all at the same time.

Want this kind of readily-absorbable, human-oriented Power BI instruction for your team? Hire us for a private training at your facility, OR attend one of our public workshops!