Recently Microsoft added the Table Information page to Business Central. See here. And underlying are these objects: Table Information (8700, List) Table Information (2000000028) So I was thinking, hey let’s use this in Power BI.
Recently James Crowter wrote an excellent article about table extensions and how they affect performance. In short, table extensions are great for flexibility and ease of development, but performance decreases when the number of table extensions is adding up. Especially when table extensions are used for hot tables. With hot tables I mean tables that are used often, like Item, Customer, Sales Line, Item Ledger Entry, etc.
It’s actually really difficult to come up with a simple demo query to prove this though (the Power Query engine is too clever about not evaluating things it doesn’t need for the final output of a query), but it’s fairly easy to understand the principle. Whenever you have an expression that returns a table something like this:
In Microsoft Dynamics 365 Business Central, there is a table where customer information is stored. In addition, there is a table for vendor data, a table for item data, and so on. Tables let you organize and structure the data within your solution. There are different types of tables based on their technical implementation as well as their functional use.
The order of the columns in a table in a Power BI dataset doesn’t matter all that much, especially because the Fields pane in Power BI Desktop ignores the original column order and lists the columns in a table in alphabetical order. However there are a few situations where it is important, for example when you are using the DAX Union() function in a calculated table: as the documentation states, when you use Union() “Columns are combined by position in their respective tables”. You might also find it irritating if the columns you see in the Data or Relationships panes in the main Power BI Desktop window make it hard to browse the data or create relationships.