Please note javascript is required for full website functionality.
MVP

Blog

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 FunctionsOne 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!

Newsletter