← Back to Chapters

Creating Multiple Tables from One File

? Creating Multiple Tables from One File

✨ Quick Overview

In professional Power BI projects, data often arrives in a single "flat" file (like a CSV export). This lesson explains how to normalize that data: splitting one file into multiple Dimension Tables and a central Fact Table using Power Query and MERGE.

? Key Concepts

  • Flat File: A table where every detail (Customer Name, Product Category) is repeated in every row.
  • Normalization: The process of removing redundancy by splitting data into related tables.
  • Surrogate Keys (Index): Creating unique numeric IDs (1, 2, 3...) to link tables instead of using long text names.
  • Merge Queries: The tool used to map text names back to their new IDs in the Fact table.

? Explanation: The Transformation Process

To turn one messy file into a Star Schema, follow these steps:

  1. Duplicate: Create copies of your source query (e.g., Dim_Product, Dim_Customer).
  2. Cleanse: In the new queries, keep only relevant columns and Remove Duplicates.
  3. Index: Add an Index Column (starts from 1) to create unique IDs.
  4. Merge: Go back to the original query and "Merge" with your new Dimensions to bring in the IDs, then delete the old text columns.
? View Raw Source Data Example 
OrderID, ProductName, CustomerName, Amount
1, Laptop, Rahul, 2000
2, Mobile, Neha, 1500
3, Laptop, Rahul, 3000

? Use Cases

  • Excel Conversions: Turning a massive "Master Sheet" into a high-performance Star Schema.
  • Database Cleanup: Replacing memory-heavy text columns with light numeric ID columns.
  • Model Scalability: Ensuring that updating a Product Name in one place updates it across the entire report.

⚡ Interactive MERGE Simulator

See how a Flat File transforms into a Normalized Fact Table.

Step 0: Original Flat File

OrderID CustomerName Amount
1 Rahul 2000
2 Neha 1500
3 Rahul 3000

? Best Practices

  • Original Stays Raw: Keep your "Source" query as a "Connection Only" (uncheck Enable Load) so it doesn't clutter your report.
  • Numeric Keys: Always link tables using whole numbers (IDs) rather than text for 10x faster performance.
  • Descriptive Naming: Rename your duplicated queries immediately (e.g., Dim_Customers, Fact_Sales).

? Try It Yourself

Practice Task:
  1. Import a flat CSV file into Power Query.
  2. Right-click the query and select Reference. Name it Dim_Product.
  3. In Dim_Product, keep only the Product column and click Remove Duplicates.
  4. Add an Index Column.
  5. In your main Sales table, use Merge Queries to bring that Index ID in.