Power Query Conventions

Modified on Sun, 26 Jul at 4:27 AM

Overview

QQube uses a consistent naming and applied-step strategy for Power Pivot and Power BI data models.

The same model structure and Power Query conventions are used in both products so tables, transformations, and field names remain predictable across every QQube analytic.

Design Strategies

Shared Model Design

Use the same data model structure for both Power Pivot and Power BI.

Consistent Applied Steps

Apply the same transformation sequence to each dimension and FACT table across all data models.

Rename Fields in Power Query

Perform field naming during the Power Query stage rather than waiting until the model stage.

Extend Calendar Functionality

Use M language applied steps to add calendar behavior and sorting fields that are not supplied directly by the source data.

Table Naming Standards

FACT tables are always displayed in uppercase.
Flexible Period calendar dimensions use the name Calendar because the date is assumed to be the transaction date.
Other calendar dimensions use the complete date description after a hyphen, such as Calendar - Due Date or Calendar - Transaction Date.
All dimensions appear alphabetically below the FACT table.

QQube Power Query table naming and alphabetical dimension organization

List Dimensions

The Class dimension illustrates the standard applied-step design used for QQube list dimensions.

Modify an Applied Step

An applied step can be changed in either of two ways:

Edit the step through the Power Query interface.
Edit the M language directly in the Advanced Editor.

Power Query applied steps for the QQube Class dimension

Use the Advanced Editor When Necessary

QQube uses the Advanced Editor instead of, or together with, the manual interface. If a renamed column is later removed, its alias can remain in the M language and may need to be removed directly through the Advanced Editor.

Power Query Advanced Editor showing the QQube Class dimension M language

Never Remove Linking Fields

Fields such as LinkForClassID connect the dimension to the FACT table. Removing a linking field breaks the model relationship.

Account Dimensions

Account dimensions in Flexible Period data models contain two additional applied steps.

Sign to Apply

Creates a field that simplifies financial-statement signage and the associated DAX measures.

Changed Type

This step enforces the required data types for two fields:

Account Has Children is assigned the logical data type.
Sign to Apply is assigned the integer data type.

Power Query applied steps for a QQube Account dimension

Calendar Dimensions

QQube calendar dimensions are designed to accomplish two objectives:

Avoid the need to designate the table manually as a Date table.
Reduce the size of the calendar dimension for improved performance.

Tactic 1: Limit the Calendar Date Range

Retrieve the maximum applicable date from the FACT table.
Retrieve the minimum applicable date from the FACT table.
Filter the calendar rows to the resulting date range.

Tactic 2: Create Consecutive Calendar Numbers

Use M language formulas to create consecutive numeric fields for:

Year Quarter Number.
Year Month Number.
Year Week calculation.
Day Integer calculation.

Tactic 3: Enforce Integer Data Types

Assign an integer data type to each consecutive-number field created in Tactic 2.

Power Query applied steps for a QQube Calendar dimension

Calendars That May Contain No Dates

When a calendar may have no applicable dates, such as when Vehicle Mileage is not enabled, QQube uses a try...otherwise M language construct to prevent refresh errors.

FACT Tables

FACT tables follow the same general applied-step rules used for list dimensions, including consistent naming, column selection, renaming, and data-type handling.

Expected Result

Power Pivot and Power BI models use the same predictable naming, applied-step sequence, relationship fields, calendar logic, and data types across all QQube analytics.