Ten tips for naming your tables, columns and measures in Power BI and otherwise documenting and organising your data model.
Working with Sage 50 accounts data, I have spent a lot of time in the last 18 months considering how best to name my tables, columns and measures in Power BI. It sounds simple and almost insignificant compared with some kind of super-calculation in DAX, but it is really important. What is the point of some kind of super-calculation if it is given a name that nobody understands or if nobody can find it in your Power BI model?
Coming up with the right name for a field is a fine balance between many things. For example; if I create a measure and call it [Sales this year] what do I mean? Do I mean gross or net sales? This calendar year or this financial year? Do I mean just the sales invoice value or do I take into account any journals that might have been applied to manage revenue recognition? Different answers to any of these questions will produce a different number for “Sales this year”.
As it happens, I do use [Sales this Year] as a name for a Measure in my model. I manage the ambiguity by providing a more precise description in the Field Properties and by consistent use throughout my model of the terms “Sales” and “this Year”.
Of course there is no single correct way to manage naming the fields and tables in your Power BI model, but what I attempt to share In this article are my own thoughts and best practice tips that I have pulled together after a lot of thinking, reading and working with Sage 50 data in Power BI.
Who is your audience?
Of course this is an obvious question, and one that becomes really important if you are producing a report for a single company. Many companies have their own specific terminology - so why not use that? “Sales” might be called “Revenue” or “Sales Orders” might be called “Bookings”. Unfortunately I don’t have that luxury since I am in the business of producing standard model for use by multiple clients. The best I can do is to use generally common terminology and then document what I mean by this. My clients can then rename things to the way they want if that suits them. If you are a practicing accountant producing reporting solutions for multiple clients then you probably face the same situation.
Content seriesView full content series
Consistent use of the same terms and their meanings
It is likely that in your model you will have the same concepts repeated many times, like “this Year”. Used loosely, this term can have many meanings. If you are consistent in what you mean by “this Year” throughout your data model then this will aid understanding. In my case, what I mean by “this Year” is the current financial year of the company’s dataset. Additionally, since Sage 50 processes and stores invoices differently depending on whether they were entered via the regular Sales Invoices module or through the Batch Invoices module I use different names to identify these invoice values. “Sales” always refers to the net invoice amount for transactions entered through the Sales Invoice module.
Your source dataset
Your source dataset will already have named tables and columns, so why not keep them? There is a simplicity to this and a precision that makes sense. Until you consider point 1 above. If you are working as I am with Sage 50 data, then the chances are that your dataset is a collection of ODBC tables with camel-hump spellings, underscores and abbreviations galore. For anyone who is not intimately familiar with the Sage 50 ODBC tables and field names, this is a recipe for confusion.
I give you two examples:
This is a field in the Sales Order Items table. What do think that it means?
The quantity of this item that has been ordered but not despatched yet?
The quantity of this item that has been despatched?
Of course these two meanings are the polar opposite and it may surprise you to learn that the former is correct. In my model I call this [Qty Undespatched].
This is a field in the Sales Invoice Items table. Surely it is obvious what this means? Well not really. Because this [NetAmount] field displays as a positive amount regardless of whether the transaction is a sales invoice or a sales credit. To get the number correct in Power BI you need to check to see if the transaction is a credit and if so, multiply by minus one.
At a minimum I would recommend that you add a more precise description to this field in the Field Properties pane, then hide it from view. Better still, if you can manage without it, remove this field from your data model either from the data source or in Query Editor. Since I have a corrected field in my data feed that I call [Sales Amount] I remove [NetAmount] from my model in Query Editor.
Hide unused fields and tables
If you have fields or tables that you are not using and are unlikely to use in your model you can hide them. Just right-click on the field or table in the Fields pane in Power BI Desktop and select the option to “Hide”.
You can choose to “View Hidden” where you can see these hidden elements but they are greyed out, or if you uncheck “View HIdden” the disappear from view altogether.
This is easily undone by anyone using your model in Power BI Desktop, so if you are distributing your Desktop model then I would recommend that you still pay proper attention to labeling and documenting this field. If you are publishing your model to Power BI Service then your users will not be able to see or unhide these hidden fields or tables.
Remove unused fields and tables
If you really don’t want a particular field or table then just remove it as it comes in using Query Editor or from the original data source if you can.
One of the nice things about Query Editor is that you can use it to create new derived fields that are more useful to you and then remove the original field that you no longer want. Your new derived fields will still work ok. I have a short video here that shows how to do this, transforming a text field into three derived fields then removing the original text field.
Using Query Editor to remove a field is not as drastic as removing it from your data source and can easily be undone by deleting this “Applied Step”.
Power BI Dashboard “Ask a Question” and the use of Synonyms.
A Power BI feature that I really like is “Ask a question”. It is kind of like Googling a question about a dataset you have loaded in Power BI. Out of the box, this works by reading your table names and field names and trying to interpret on the fly the question that you are asking. Inherently Power BI also understands phrases like “last month”. So if you have used nice plain English names for your tables and fields then this function will work pretty well without you having to do anything.
If you are stuck with a field name that is not using natural language (maybe for brevity for example you have had to shorten “Quantity” to “Qty”), you can get around this, or improve it by adding in your own synonyms for particular fields or even table names - for example you may want to define a synonym for “Product” as “Stock Item” or for “Product Code” as “Stock Code”. You can do this in Power BI in the “Relationships” view of your model.
But, if you want to keep life simple for yourself and still use the “Ask a Question” function then make sure that the names you use for your tables and fields are the words that you would use in plain English to describe the table or field.
Length of field names
There is a big trade-off between the length of a field name and the precision of its meaning. For example, “Sales” is nice and simple. Just one word. But “Net Item Sales Amount” is a lot more precise and descriptive.
Long field names present a problem though in your visualisations taking up precious space on your report canvas. Also, if there is a field that is used very frequently throughout your report then maybe a short name is ok. Like a kind of company shorthand.
No matter how hard you try though, it is likely that at some point you will still end up with some long field names and struggle for space in your visualisation. Here are three practical things that you can do about it.
Word wrap column headers
If you still need a field name that runs to a view words you can opt to word wrap column headers like this:
Increase area used for axis labels in charts
For bar charts in particular, long field names can present a problem as shown here. The January 2018 Power BI update includes a nice feature whereby you can increase the area used for axis labels up to 50% of the chart.
Custom “Horizontal Bar Chart” visual
Another way to save space on your canvas if you have long field names is to use the custom “Horizontal Bar Chart” visual. This writes the field name on top of the bar as shown here:
Field Properties Pane
In the December 2017 update to Power BI, MIcrosoft introduced the Field Properties Pane to Power BI Desktop. This enables you to add a description to each field in your model that will come up as a tooltip. For example in my ‘Product Sales’ table I have a field called [Cost (Extended) ]. This is the extended cost price of a product, using the Last Cost Price from the date of the transaction. It is important to specify that it relates to the cost at the time of the transaction, because there is a field in Sage that gives a Last Cost Price for a Product. That field is the current Last Cost Price.
Start the field names of similar fields in the same way
Since field names are sorted alphabetically when displayed in the Fields pane in Power BI, if you start these field names in the same way, they will appear together. This makes it easier to find the field that you are looking for.
For example if you have a group of Measures that summarise sales by period you can name them:
Sales last 7 days
Sales last 28 days
Sales last month
Sales this month
In this way, they all appear together and can be found easily.
Group Measures into a “Measures Table”
Another common problem related to naming and finding Measures that you have created in your Power BI model is that although Measures are defined as having a “Home” table, they operate across your entire model and not just within the table that they are resident in. This can make them difficult to find. A technique to get around this is to group your Measures together into a Measures table.
Group Queries within Query Editor
Within Query Editor it is possible to group queries together around a particular topic for example. I have done this here as an example for Sage 50 Nominal Accounts.
This can be useful for anyone using Query Editor in Power BI Desktop. This does not however create groups within the field pane in the Power BI Report view either in Desktop or on Power BI Service.
In my opinion therefore this is useful, but only for people who are editing the data model using query editor in Power BI Desktop.
Paying a little bit of careful attention to how you name and organise your queries, tables and fields in Power BI will make a big difference to anyone using your model. None of the tips suggested here are difficult. All they require is a bit of thought. It will always be a trade-off and there is no such thing as a perfect solution, but it is well worth the effort to do your best and think of the end user of your reports, or anyone who may need to modify your model in the future.
You are welcome to try out my Power BI Desktop template file directly with the Suntico BI connector for Sage 50.
I am sure that some of you may have your own thoughts and suggestions and I would love to hear them.
YouTube video I created to show how to use the Power BI Query Editor to extract date information from text.
YouTube video highlighting the power of Q&A working with a well-named data model.
Using the Field Properties Pane to document your tables and fields.
How to create a Measures Table to make it easy to find your Measures.
How to connect your Sage 50 system to the model I have presented in this article.
About Hugh Johnson
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs, sales reports & forecasts, liquidity & cash flow reports, stock reports, and accounts receivable analysis from Sage, Xero, Exchequer, Microsoft Business Central, Iris, and CCH.
I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year).
My personal passions are high-performance Fireball dinghy racing and food.