Power BI: Flatten a snowflake dimension with Power Query

Key insights

• A snowflake is a dimension table related to another dimension; Power BI can filter through that relationship.
• Merge Queries can add selected lookup columns to a dimension using a left outer join.
• Flattening can put attributes such as country beside reseller name and business type.
• A shared Geography table can make one slicer filter both customer and reseller sales.
• Keep a shared dimension separate when you want its filters to affect multiple sides together.

Power BI modelers can flatten a snowflake dimension by merging lookup attributes into a dimension table, making those fields easier to find and use in reports. This is useful when report authors need related attributes together, but a shared dimension may need to stay separate if its filters should affect multiple areas of a model. Pragmatic Works demonstrates the modeling choice with Reseller, Geography, and sales tables from AdventureWorks.

What it does

A snowflake schema has a dimension related to another dimension, rather than every descriptive attribute residing in one table. In the example, Reseller holds a Geography Key, and Geography holds the country name. The relationship lets Geography filter Reseller and then reseller sales; the model can work correctly even though the related attributes are split across tables.

Flattening means bringing selected lookup fields into the dimension that report authors work with. Here, the demonstration merges Geography into Reseller and adds a Country Region column alongside Reseller Name and Business Type. That can make the model easier to navigate without copying every column from Geography.

The choice is not simply whether a snowflake is good or bad. It is also about what the dimension represents and which results should respond to a filter. A shared Geography table can connect customer geography to Internet Sales and reseller geography to Reseller Sales, so one country selection can affect both. That behavior can be intentional when users want a combined view of activity in a country, but misleading if the two visuals mean different things by “France.”

Who it is for and what you need

This technique is for people building or maintaining Power BI semantic models, especially modelers who want descriptive fields to be easier for report authors to locate. The example focuses on the Reseller and Geography tables, with Internet Sales and Reseller Sales used to explain shared filtering. It does not identify a required license, Power BI edition, or minimum product version.

The demonstration uses Power Query Editor and a Geography Key that matches between Reseller and Geography. It does not state a rollout stage or regional availability; this is a modeling technique, not a described product rollout. If you are reviewing a Power BI model, first establish whether the dimension is used by just one parent or shared across different parts of the model.

Also decide what a country filter is meant to represent. Customer location and reseller location are different business meanings in the example. A single shared Geography dimension makes those paths respond together, while separate fields can let report users filter the two sides independently.

How it works

1. In Model view, identify the dimension-to-dimension relationship. In the example, Reseller connects to Geography by Geography Key, creating the snowflake.
2. Open Power Query Editor and select the Reseller table. Choose Merge Queries, select Geography as the other table, and match the Geography Key columns.
3. Use a Left Outer join. This keeps every row from Reseller, including a reseller without a matching Geography row, while bringing in matching Geography data.
4. Expand the merged Geography table. Deselect the columns you do not need and select only the country field. Turn off the column-name prefix and rename the new field Country Region to distinguish it from other country fields.
5. Apply the query changes and check the model and report behavior. The added field now sits with the reseller attributes, but the existing relationship still determines how filters propagate.

The key distinction is between a displayed label and a relationship. Replacing the country field used in a visual does not, by itself, remove the Geography-to-Reseller relationship. In the demonstration, the relationship is deleted after the merge so the report can use separate country fields: the Geography slicer filters the customer side, while Country Region filters the reseller side.

That relationship change is a separate modeling decision from adding a column. Check the intended behavior before removing a relationship: if the shared dimension is meant to make both sales areas move together, keeping it is appropriate. If the report needs independent meanings and filters, separate fields and the corresponding relationship design can support that distinction.

Limitations and open questions

The example does not establish a universal performance benefit. The presenter says the merge runs at refresh while relationship filtering is used during queries, but also says the difference would not be noticeable for the 700 resellers in the example. Treat usability and intended filter behavior as the decision points for a small model, rather than assuming that flattening will make every report faster.

The demonstration advises expanding only the attributes needed. It argues that repeating a country value across 700 reseller rows has little memory impact in this example, but does not quantify results for other models. It also cautions against applying the same idea to fact tables, where repeating descriptive text across many rows is a different situation.

Keep the dimension separate when it is shared and users should filter multiple areas together, when it has its own measures or fact table, or when it needs to stand alone as a slicer. The available demonstration does not cover other design constraints, licensing, or how to test a model after broader changes. Validate the relationships and report results against the meaning users expect.

Sources

• Pragmatic Works demonstration of flattening a Power BI snowflake

Frequently asked questions

How do I flatten a dimension in Power BI?

In Power Query Editor, select the dimension query, use Merge Queries to join the lookup on matching keys, then expand only the columns you need. The example uses a Left Outer join and adds a country field to Reseller.

When should I keep a snowflake dimension in Power BI?

Keep it separate when it is shared and you want filters to affect multiple parts of the model together, when it has its own measures or fact table, or when it needs to stand alone as a slicer.

Does flattening a Power BI dimension make reports faster?

The demonstration says the merge runs at refresh and a relationship is used during queries, but reports no noticeable difference for its 700-reseller example. It does not establish a general performance result.

Does merging a country column remove its relationship in Power BI?

No. Adding a column through a merge does not itself remove the existing relationship. The example deletes the Geography-to-Reseller relationship separately to let country filters act independently.