Data Import and Transformation

Data import in Power BI is the process of loading data from a source into the in-memory VertiPaq engine, where it is compressed and stored inside the semantic model for fast query performance. Import mode is one of three storage modes alongside DirectQuery and DirectLake.

Step 1: Connect to a data source In Power BI Desktop, click Get Data in the Home ribbon. Choose from hundreds of connectors: relational databases (SQL Server, PostgreSQL, Oracle, Snowflake), files (Excel, CSV, JSON, Parquet), cloud services (Azure, Salesforce, SharePoint, Fabric lakehouse/warehouse), and OData feeds. After selecting a connector, provide connection details and credentials.

Step 2: Select tables or write a query A navigator dialog shows available tables, views, or sheets. Check the items you want to import. For databases, you can write a native SQL query in advanced mode to pre-filter or join data at the source — this leverages query folding to reduce the data transferred to Power BI.

Step 3: Transform data in Power Query Clicking Transform Data opens the Power Query Editor. Apply transformation steps: filter rows, remove columns, rename headers, change data types, merge/append tables, pivot/unpivot, and handle nulls. All steps are recorded as M code and replayed on every refresh.

Step 4: Load into the semantic model Click Close and Apply to load the transformed data into Power BI's in-memory store. In the Model view, define relationships between tables, create DAX measures, and configure RLS.

Step 5: Configure refresh in the Service After publishing, scheduled refresh in the Power BI Service re-runs the Power Query steps and reloads the semantic model. Import-mode models reflect data only as of the last refresh.

Alternatives to full import:

• DirectQuery — no data is imported; queries go live to the source on each visual interaction. Best for very large or frequently changing data where import latency is unacceptable.
• DirectLake (Fabric only) — reads Delta/Parquet files directly from OneLake without a traditional refresh cycle, combining near-Import performance with near-real-time freshness.
• Composite models — mix Import and DirectQuery tables in a single semantic model for balanced performance and freshness.
• Incremental refresh — Import only new/updated rows each cycle while retaining historical partitions, dramatically reducing refresh duration for large tables.

Data transformation in Power BI refers to the process of reshaping, cleaning, and enriching raw data so it is structured correctly for analysis in the semantic model. It is performed primarily in the Power Query Editor (using M code under the hood) before data is loaded into the model.

Why it is important:

• Data quality — raw data from source systems often contains nulls, duplicates, inconsistent formats (e.g., dates stored as text, mixed-case strings), and errors. Transformation corrects these issues before they propagate into reports and mislead decision-makers.

• Correct schema for modeling — the VertiPaq engine performs best when tables are structured in a star schema: narrow, clean dimension tables and fact tables without unnecessary columns. Transformation enables denormalizing, splitting, or restructuring tables to achieve this layout.

• Performance — removing unused columns and filtering rows in Power Query reduces the volume of data loaded into memory. Fewer rows and columns means smaller model size, faster refresh, and snappier query performance.

• Reusability and consistency — transformation logic defined in Power Query is centralized and replayed on every refresh. Unlike one-off Excel manipulations, the steps are self-documenting and repeatable.

Common transformation operations:

• Filtering rows and removing columns not needed in the model
• Changing data types (especially date/numeric columns mistyped as text)
• Removing duplicates and handling null/error values
• Splitting or merging columns (e.g., full name into first/last)
• Pivoting and unpivoting tables
• Merging (joining) queries and appending (stacking) queries
• Creating conditional columns and grouping/aggregating data

Power Query vs. DAX: A key principle is to do row-reduction and schema work in Power Query, and business logic calculations (ratios, running totals, time intelligence) in DAX measures. Applying heavy transformations in DAX calculated columns is less efficient than doing equivalent work in Power Query, because DAX calculated columns are computed at model load time and stored, whereas Power Query operations can often be folded back to the source.

M (officially called Power Query Formula Language) is the functional, case-sensitive language that runs behind every step in the Power Query Editor. When you apply a transformation in the GUI — filtering a column, changing a data type, merging two tables — Power BI writes M code for it. Advanced users read and write M directly to build transformations the GUI does not expose.

Key characteristics of M:

• Functional and lazy — M is a functional language where expressions define what data should look like, not a sequence of imperative steps. Queries are only evaluated when data is loaded or previewed.
• Structured expression language — everything in M is an expression that returns a value. Values can be primitive types (text, number, date, logical), or structured types (list, record, table).
• Let…in syntax — M query logic is written as a let block that defines named steps, and an in clause that specifies the output of the query:

let
    Source = Sql.Database("server", "db"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    FilteredRows = Table.SelectRows(Orders, each [Status] = "Active"),
    RenamedColumns = Table.RenameColumns(FilteredRows, {{"OrderDate", "Date"}})
in
    RenamedColumns

How M is used in Power BI:

• Automatic generation — every GUI action in the Power Query Editor appends an M step automatically. Most users never write M directly.
• Advanced editor — the Advanced Editor (View > Advanced Editor) shows and allows editing of the full M query. This is used when you need logic the GUI cannot express, such as dynamic parameters, custom functions, or conditional branching.
• Custom functions — reusable M functions can be created to apply the same transformation across multiple queries (e.g., a standardized date-parsing function).
• Parameters — M parameters allow queries to be parameterized, enabling environment-switching (dev/test/prod server names) or date-range filtering without hardcoding values.
• Query folding — M transformation steps that can be translated back into native source queries (SQL, OData, etc.) are "folded" to the source, so the filtering/aggregation happens at the database rather than in Power BI memory. Understanding which M operations fold and which break folding is important for performance tuning.

Gotcha interviewers probe: M is not the same as DAX. M runs at data load/refresh time in Power Query to shape data. DAX runs at query time in the semantic model to calculate measures and calculated columns. They serve different stages of the Power BI pipeline.

Handling large datasets efficiently is one of the most practically important skills in Power BI, and interviewers probe this in depth. The strategies span storage mode selection, Power Query optimization, model design, and Fabric architecture.

1. Choose the right storage mode

• Import — fastest query performance but requires the full dataset to fit in memory. Practical up to a few hundred million rows depending on cardinality and column count. Not suitable for multi-billion-row tables without aggressive aggregations.
• DirectQuery — no data stored in Power BI; queries go to the source live. Works for any size but query performance depends on the source. Avoid complex DAX with DirectQuery as it generates inefficient SQL.
• DirectLake (Fabric only) — reads Delta/Parquet files from OneLake directly without importing. Combines Import-level performance with near-real-time freshness, and scales to very large tables because it reads only the columns needed per query. The recommended mode for large datasets on Fabric.
• Composite models — combine DirectLake or DirectQuery for large fact tables with Import-mode dimension tables to get fast filtering with live fact data.

2. Optimize Power Query for large imports

• Apply filters early in the query steps to reduce row count before merges or type conversions.
• Remove unused columns as early as possible to minimize data transferred and stored.
• Ensure query folding is active — verify by right-clicking a step and checking whether "View Native Query" is available. Steps that do not fold pull all rows into memory and transform them in Power BI rather than at the source.
• Avoid operations that break folding unnecessarily (e.g., certain custom column formulas or adding an index column early in the pipeline).

3. Use incremental refresh

Define an incremental refresh policy on large Import-mode tables to partition data by date range. On each refresh, only the recent partition (e.g., last 3 days) is reloaded, while historical partitions remain untouched. This reduces refresh duration from hours to minutes for tables with years of history. Incremental refresh requires Premium Per User (PPU) or Premium/Fabric capacity.

4. Aggregation tables

Create a pre-aggregated summary table (e.g., sales by month by region) alongside the full-grain fact table. Power BI automatically routes high-level queries to the fast aggregation table and falls through to the detail table only when a user drills to grain level. This technique, combined with a composite model, delivers interactive performance on billion-row datasets.

5. Semantic model design

• Use a star schema — narrow dimension tables and a central fact table. Avoid wide flat tables with many low-cardinality repeated text columns (they compress poorly in VertiPaq).
• Keep cardinality in check — high-cardinality text columns (free-form notes, URLs) inflate model size significantly; consider excluding them or summarizing.
• Use integer surrogate keys for relationships rather than text keys; integer joins are faster and more memory-efficient.

6. Monitor with VertiPaq Analyzer / DAX Studio

Tools like DAX Studio and the open-source VertiPaq Analyzer report table and column sizes, cardinality, and relationship efficiency. Use them to identify which columns are consuming the most memory and where to focus optimization effort.

Data cleaning in Power BI is the process of identifying and correcting errors, inconsistencies, and quality issues in raw data so it is reliable for analysis. The primary tool is the Power Query Editor, accessed in Power BI Desktop by clicking Transform Data after connecting to a source.

Common data cleaning tasks and how Power BI handles them:

Removing duplicates Use Remove Duplicates (Home ribbon or right-click a column) to eliminate duplicate rows based on all columns or a specific key column. This is commonly applied to dimension tables to ensure one row per entity.

Handling null and missing values Use Replace Values or Fill Down/Fill Up to address nulls. You can filter out rows where a critical column is null, replace nulls with a default value (e.g., "Unknown" for text, 0 for numbers), or use conditional columns to flag incomplete records for review.

Correcting data types Power Query auto-detects data types but is often wrong, especially for date and number columns stored as text in source files. Explicitly set data types for every column. Dates stored as text will not work correctly in DAX time intelligence functions until cast to the Date type.

Standardizing text Use Transform > Format options: Trim (remove leading/trailing spaces), Clean (remove non-printable characters), UPPERCASE, lowercase, or Proper Case. Inconsistent casing (e.g., "New York" vs "new york" vs "NEW YORK") causes grouping errors in visuals.

Splitting and combining columns Split a single column (e.g., "First Last" full name, or "City, State" combined fields) into separate columns using delimiter-based or positional splitting. Conversely, merge columns with a custom separator.

Replacing incorrect values Use Replace Values to fix known bad data — for example, replacing "N/A" text with null, or correcting a misspelled category name that appears consistently in the source.

Filtering out irrelevant rows Remove test records, header rows embedded mid-file, or out-of-scope data early in the query to reduce model size and avoid contaminating aggregations.

Handling errors Power Query surfaces errors at the cell level (e.g., a type conversion that failed). Use Remove Errors to drop those rows, or Replace Errors to substitute a default value, depending on the business requirement.

Column quality profiling The Power Query Editor's column profiling tools (View > Column quality / Column distribution / Column profile) show the percentage of valid, null, and error values per column, along with value distribution histograms. This makes it easy to spot quality problems before loading data into the model.

Key principle for interviews: data cleaning is best done in Power Query rather than in DAX, because Power Query transformations run at refresh time and reduce the data entering the model. Cleaning data with DAX calculated columns is technically possible but wastes memory by storing the corrected values alongside the original bad data.