Power Query Conventions | CLEARIFY

Version 10 Power Query Implementation

Strategies:

• Same model for both Power Pivot and Power BI
• Applied steps for every dimension and fact table consistent in every data model
• Naming performed at this stage rather than model stage
• Augment calendar functionality with M Language applied steps

Naming

• FACT tables are always in caps
• Calendar Dimensions:
  • Flexible Period Data Models use the name "Calendar" as it is always assumed it is a transaction date.
  • All other data models use the proper full name prefixed by a hyphen.  e,g,  Calendar - Due Date or Calendar - Transaction Date
• All form of dimensions are in alphabetical order underneath the FACT table

List Dimensions

Using the Class DIMENSION as an example, there are two ways to change or modify a step:

We use the advanced editor in lieu of the manual steps above, OR in conjunction.  If you remove columns, the alias name you gave it can only be "erased" through the advanced editor.

We highlighted the "LinkForClassID" to spotlight the fact that linking fields should never be removed - it is the connection to the FACT table.

Account Dimensions

Two additional applied steps appear for Account Dimensions in Flexible Period Data Models

1. Sign to Apply. Makes it easier to create proper signage in financial statements and associated DAX measures.
2. Changed Type. Applies to two fields:
   1. Ensures type of "logical" for the new QQube field "Account Has Children"
   2. Ensures that the Sign to Apply applied step is type of "integer'

Calendar Dimensions

We followed two strategies in the Calendar Dimensions

1. Alleviate the necessity of having to designate the dimension as a "date" dimension
2. Minimize the size of the dimension for speed considerations

Tactic One

• Retrieve the maximum date in the FACT table (for the appropriate type of date)
• Retrieve the minimum date in the FACT table (for the appropriate type of date)
• Filter the rows to match those retrieved values

Tactic Two

• Employ consecutive number M language formulas for:
  • Year Quarter Number
  • Year Month Number
  • Year Week calculation
  • Day Integer Calculation

Tactic Three

• Assure integer type for all items in Tactic Two

NOTE:

• If a calendar has the possibility of no dates e.g. vehicle mileage is not turned on - then we use a "try-otherwise" M language construct to avoid errors.

FACT Table

These follow the same applied step rules as list dimensions.