Using Reference Tables as Primary Tables in Power BI

In Power BI, it is crucial to understand how tables interact within the data model, particularly when using reference tables. This guide outlines how to use a reference table as a primary table while maintaining all steps from applied transformations.

Key Concepts

• Reference Table: A table created from another table where transformations (such as filters, column removals, etc.) are applied, but the data remains linked to the original table.
• Primary Table: A table that serves as a main dataset for analysis, usually containing the most relevant data for reporting.
• Applied Steps: Transformations performed within Power Query that can be retained for reference or adjustments.

Steps to Use a Reference Table as a Primary Table

1. Create the Reference Table

You can create a reference table based on an existing table from the Power Query Editor. To do this:

1. Open Power Query Editor.
2. Select the Source Table.
3. Right-click on the table and choose Reference. This creates a new query based on the selected table.

2. Apply Transformations

Perform the necessary transformations for your reference table, such as filtering rows or extracting columns. These transformations are stored as "Applied Steps" in the query.

3. Load the Reference Table

Once you’ve completed your transformations:

1. Click Close & Apply to load the reference table into your Power BI model.

4. Set Relationships

Set the necessary relationships between your reference table and other tables in the data model. You can establish many-to-one relationships, where the reference table serves as the "one" side, allowing extensive data analysis.

5. Maintain Steps in New Data Table

If you want to retain the applied steps from this reference table for future use:

• Duplicate the Reference Table: In Power Query, right-click on the reference table, and choose Duplicate. This will create a new instance that retains the applied steps.

• Modify as Required: You can modify this duplicated table without altering the original reference table and keep all transformations if needed.

Example Code Snippet

To demonstrate retaining applied steps through M code, here’s a simple example where you filter a reference table for specific data.

let
    Source = YourSourceTable,
    ReferenceTable = Table.SelectRows(Source, each [Column1] = "Value"),
    FilteredTable = Table.SelectColumns(ReferenceTable, {"Column1", "Column2"})
in
    FilteredTable

Explanation of Code:

• Source retrieves the original table.
• ReferenceTable filters the data based on a condition.
• FilteredTable selects specific columns from the reference table, retaining the transformations.

Conclusion

By following the outlined steps, you can successfully use a reference table as a primary data source while preserving all transformations and applied steps. This approach enhances data management and allows for more organized reporting within Power BI.

Ensure you are familiar with different Power BI functionalities and consider upskilling through the Enterprise DNA Platform for in-depth learning on data modeling and Power BI techniques.

If you have further questions or need clarification on specific steps, feel free to ask!