Denormalize a dimension

In this example, the DimProduct, DimProductSubcategory, and DimProductCategory tables are integrated into one query. This query will include all product rows, only the English language columns, and user-friendly names.

Many-to-one relationships have been defined in the source database.

1. Create three queries for each source table and disable their loads.
2. Use query group folders in the Query Editor to isolate these queries.

• The gray font indicates that the queries will not be loaded into the model.

• Each staging query references the dedicated data source query (AWorksProd) that specifies the server and database.
• In this example, the AWorksProd query has the following syntax: Sql.Database("ATLAS","AdventureWorksDW2016CTP3")

1. Create a new (blank) query and name it Product.

1. In the Product query, use the Table.NestedJoin() function to join the DimProduct and DimProductSubcategory queries created in step 1.

• A left outer join is required to preserve all DimProduct rows since the foreign key column to DimProductCategory allows null values.

1. Add a Table.ExpandColumns() expression to retrieve the necessary columns from the DimProductSubcategory table.

• The join function inserts the results of the join into a column (SubCatColumn) as table values.
• The second expression converts these table values into the necessary columns from the Subcategory query and provides the simple Product Subcategory column name.
  

• The query preview in the Query Editor will expose the new columns.

1. Add another expression with a Table.NestedJoin() function that joins the previous expression (the Product to Subcategory join) with the DimProductCategory query.
2. Just like step 4, use a Table.ExpandTableColumn() function in a new expression to expose the required Product Category columns.

• The first expression adds the results of the join to DimProductCategory (the right table) to the new column (ProdCatColumn).
• The second expression adds the Product Category columns required and revises the EnglishProductCategoryName column to Product Category.
• A left outer join was necessary with this join operation as well since the product category foreign key column on DimProductSubcategory allows null values.

1. Write an expression that selects the columns needed for the load to the data model with a Table.SelectColumns() function.
2. Add a final expression to rename these columns via Table.RenameColumns() to eliminate references to the English language and provide spaces between words.

• The preview in the Query Editor will present the results of steps 1 through 8:

• It's not necessary to rename ProductKey since this column will be hidden from the reporting layer.
• In practice, the product dimension would include many more columns.
• The denormalized Product query can now support a three-level hierarchy in the Power BI Desktop model to significantly benefit reporting and analysis.