Power Query: ToolTip
16 September 2020
Welcome to our Power Query blog. This week, I look at how to use M metadata functions to create a ToolTip for columns.
I looked at some uses for metadata nearly three years ago in Customising Custom-Built Functions. One of the metadata functions I looked at was:
Documentation.FieldDescription as text
Description to show next to the display name.
As I described in that blog, these functions can be found at https://msdn.microsoft.com/library/mt807488.aspx.
Back then, when I looked at how Documentation.FieldDescription could be used, I was making a function I had created more user-friendly; this time, I will use it as I create a table.
I start by creating a new blank query. In Excel on the ‘Data’ tab, I choose ‘New Query’ and select ‘Blank Query’ from the dropdown next to ‘From Other Sources’:
I can then access the ‘Advanced Editor’ from the Home tab.
The M code I am going to use will create a simple table of employees.
The M code I have created is:
let source =
#table({"Name", "Secret"}, {{"Mary Smith", "67"}}),
tableType =
type table[Name = Text.Type, Secret = Number.Type]
meta [
Documentation.FieldDescription =
[Name = "Full Name", Secret = "Age!"]
],
replaceType = Value.ReplaceType(source, tableType)
in
replaceType
In this M code, I first create a table by defining my source as an #table – for more on this, see Bring it to the Table. I then create a tableType, where I define the column properties of a table and include the Documentation.FieldDescription() function. Finally, I ensure my table uses that tableType (using Value.ReplaceType).
When I click ‘Done’, I get my table:
If I hover over the title of Secret, I get a ToolTip:
I can see that the secret column holds Mary’s age (shhh).
There are some limitations to this. For example, if I remove Name:
I find I can no longer see the ToolTip for Secret.
This is because the table type has changed, so the link to the metadata is lost. If I delete this step, I can see the ToolTips again:
I can also lose the Tooltips on the table if I change the type of one of the columns:
I should therefore ensure that my tableType with metadata functionality is assigned to my table at a point where I am not planning to change anything that affects the tableType.
Come back next time for more ways to use Power Query!
