Power BI get data and Power Query transformations

#Why it matters

Every Power BI report starts with Get data, and most wrong numbers in production trace back to this step: a date read in the wrong locale, a ZIP code that lost its leading zero, a CSV comment that split one row into two. In our Data Analyst job research, Power BI appears in 80% of postings and "data cleaning" or "data preparation" sits next to it in most of them; Microsoft's PL-300 exam gives "Prepare the data" 25 to 30% of the marks. This lesson builds the loading layer you will reuse for the rest of the skill.

#The concept

The general Power Query editor (applied steps, merges, unpivot) is taught in Excel. This lesson covers what is specific to Power BI: connectors, storage mode at connect time, types and locales, profiling, staging queries and where cleaning should live.

#Connectors you will meet at work

The gateway (the on-premises data gateway) is a Windows service that lets the cloud reach data inside your company network. Knowing which sources need one is a standard interview question and part of the RLS and refresh lesson later in this skill.

#Import or DirectQuery, decided at connect time

When you connect to a database (SQL Server, PostgreSQL, Snowflake and others), the dialog asks for a Data Connectivity mode:

• Import copies the data into the model. Visuals are fast; data is as fresh as the last refresh.

• DirectQuery leaves the data in the database and sends a query each time a visual renders. Data is live; visuals are as fast as the database.

Files (CSV, Excel, Folder) only support Import. Choose Import unless you have a clear reason not to; the advanced lesson on storage modes covers the trade-off in depth. You can change a table's mode later, but switching from DirectQuery to Import and back is not always possible, so decide deliberately.

#Data types and locale

Power Query assigns a type to every column. Two things surprise people:

1. Type detection reads only the first 200 rows. If a column holds numbers for 200 rows and text after that, the automatic Changed Type step turns the later values into errors.

2. Text-to-type conversion uses a locale. By default it is your machine's or file's regional setting. Under a decimal-comma locale such as Portuguese (Brazil) or German, the text 58.90 is read as 5,890, because the full stop is a thousands separator there. Under a day-first locale such as English (India), 12/1/2010 is 12 January.

The fix is to state the locale on the type change. In the UI: right-click the column header, Change Type, Using Locale, pick the type and the locale of the data (not of your laptop). In M, it is the last argument of Table.TransformColumnTypes:

Table.TransformColumnTypes(
    Source,
    {{"price", type number}, {"order_purchase_timestamp", type datetime}},
    "en-US"
)

Also decide which columns are not numbers. IDs, ZIP or PIN codes and phone numbers are text. A whole-number type drops leading zeros and invites summing.

#Column quality, distribution and profile

Turn on View, Column quality, Column distribution and Column profile in the Power Query editor. You get, per column, the share of Valid, Error and Empty values, a histogram of distinct and unique counts, and full statistics for the selected column.

The trap: by default these are computed on the top 1,000 rows only. The status bar says "Column profiling based on top 1000 rows"; click it and choose Column profiling based on entire data set before you trust a 0% empty or 0% error figure. Profiling the full table is slower, so switch it on to check and off again while you build.

#Staging queries and Enable load

A query whose output is only an input to other queries should not be loaded into the model. Right-click it in the Queries pane and untick Enable load (its name turns italic). This keeps helper tables, parameters and raw copies out of the model, so users never see them and the file stays small. Disabled queries still refresh as part of the queries that reference them.

A common layout:

Create groups by right-clicking the Queries pane, New Group. Name model queries the way users should see the tables (Order Items, not olist_order_items_dataset); name helpers so their role is obvious.

#Query dependencies

View, Query dependencies draws which query feeds which, from the data source on the left to the loaded tables on the right. Use it before you delete or rename anything, and to show a reviewer where each table comes from.

#Where should cleaning happen?

A rule many Power BI teams quote: transform data as far upstream as possible, and as far downstream as necessary.

Fixing a type or a lookup in DAX (with calculated columns) works but costs memory and is invisible to anyone who reuses the data. Cleaning by hand in Excel before loading breaks the refresh the first time the export changes.

#Worked example: the Olist e-commerce folder

The Brazilian E-Commerce Public Dataset by Olist holds about 100,000 real, anonymised orders placed on the Olist marketplace between September 2016 and October 2018, split across eight related CSV files. Olist published it on Kaggle under CC BY-NC-SA 4.0 and in its own GitHub repository olist/work-at-olist-data (MIT licence), where each file has a raw URL. Download the files from the datasets folder of that repository into one folder, for example C:\Data\olist. You need these:

olist_customers_dataset.csv
olist_order_items_dataset.csv
olist_order_payments_dataset.csv
olist_order_reviews_dataset.csv
olist_orders_dataset.csv
olist_products_dataset.csv
olist_sellers_dataset.csv
product_category_name_translation.csv

The eight files have different columns, so Combine files (which appends files with the same layout) is the wrong tool. Instead, read the folder once and pick each file by name.

#Step 1: a parameter for the folder

Home, Manage parameters, New parameter: name OlistFolder, type Text, current value C:\Data\olist. Moving the files later, or switching to SharePoint, then changes one value instead of eight queries.

#Step 2: a staging query for the folder

Get data, Folder, browse to the folder, Transform data. Rename the query Olist Files, then replace its code (Home, Advanced editor) with:

let
    Source = Folder.Files(OlistFolder),
    CsvOnly = Table.SelectRows(Source, each [Extension] = ".csv")
in
    CsvOnly

Right-click Olist Files, untick Enable load.

#Step 3: one function that reads any Olist file

Home, New source, Blank query, name it fnOlistCsv, and paste:

(fileName as text) as table =>
let
    File = #"Olist Files"{[Name = fileName]}[Content],
    Csv = Csv.Document(File, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Csv, [PromoteAllScalars = true])
in
    Promoted

Three details matter here:

• Encoding = 65001 is UTF-8. The sellers and reviews files contain Portuguese characters, and the translation file starts with a UTF-8 byte order mark. Read it as Windows-1252 (code 1252) and the first header becomes product_category_name, which then fails every merge on that column.

• QuoteStyle = QuoteStyle.Csv respects line breaks inside quoted fields. The reviews file has them in 3,852 customer comments; with QuoteStyle.None every one of those comments breaks its row in two (output below).

• {[Name = fileName]} looks the file up by name and fails with an error if two files share that name, which can happen because Folder.Files also reads subfolders.

#Step 4: model queries with types and locale

Create a blank query per table that calls the function and sets types with an explicit locale. Orders:

let
    Source = fnOlistCsv("olist_orders_dataset.csv"),
    Typed = Table.TransformColumnTypes(
        Source,
        {
            {"order_id", type text}, {"customer_id", type text}, {"order_status", type text},
            {"order_purchase_timestamp", type datetime}, {"order_approved_at", type datetime},
            {"order_delivered_carrier_date", type datetime}, {"order_delivered_customer_date", type datetime},
            {"order_estimated_delivery_date", type datetime}
        },
        "en-US"
    )
in
    Typed

Order Items, where the price columns need the locale most:

let
    Source = fnOlistCsv("olist_order_items_dataset.csv"),
    Typed = Table.TransformColumnTypes(
        Source,
        {
            {"order_id", type text}, {"order_item_id", Int64.Type}, {"product_id", type text},
            {"seller_id", type text}, {"shipping_limit_date", type datetime},
            {"price", type number}, {"freight_value", type number}
        },
        "en-US"
    )
in
    Typed

Customers, where the ZIP prefix must be text and padded back to five digits (the source file already stores 09790 as 9790):

let
    Source = fnOlistCsv("olist_customers_dataset.csv"),
    Typed = Table.TransformColumnTypes(
        Source,
        {
            {"customer_id", type text}, {"customer_unique_id", type text},
            {"customer_zip_code_prefix", type text}, {"customer_city", type text}, {"customer_state", type text}
        }
    ),
    PaddedZip = Table.TransformColumns(Typed, {{"customer_zip_code_prefix", each Text.PadStart(_, 5, "0"), type text}})
in
    PaddedZip

Write Payments, Reviews and Sellers the same way (all IDs as text, payment_value as number with "en-US", review_score as whole number, dates as datetime).

#Step 5: translate the categories

The product categories are in Portuguese. A staging query Category Translation (load disabled) reads the lookup file:

let
    Source = fnOlistCsv("product_category_name_translation.csv"),
    Typed = Table.TransformColumnTypes(
        Source,
        {{"product_category_name", type text}, {"product_category_name_english", type text}}
    )
in
    Typed

Products merges it with a left outer join (keep every product, even without a match), then falls back to the Portuguese name, then to unknown:

let
    Source = fnOlistCsv("olist_products_dataset.csv"),
    Typed = Table.TransformColumnTypes(
        Source,
        {{"product_id", type text}, {"product_category_name", type text}, {"product_weight_g", type number}},
        "en-US"
    ),
    // Csv.Document returns empty text, not null, for an empty field
    EmptyToNull = Table.ReplaceValue(Typed, "", null, Replacer.ReplaceValue, {"product_category_name"}),
    Merged = Table.NestedJoin(
        EmptyToNull, {"product_category_name"},
        #"Category Translation", {"product_category_name"},
        "Translation", JoinKind.LeftOuter
    ),
    Expanded = Table.ExpandTableColumn(Merged, "Translation", {"product_category_name_english"}),
    WithCategory = Table.AddColumn(
        Expanded,
        "Category",
        each if [product_category_name_english] <> null then [product_category_name_english]
             else if [product_category_name] <> null then [product_category_name]
             else "unknown",
        type text
    ),
    Kept = Table.SelectColumns(WithCategory, {"product_id", "Category", "product_weight_g"})
in
    Kept

Two details: Csv.Document gives an empty text value (not null) for an empty field, so the EmptyToNull step is what makes the null test work for the 610 products without a category. And Table.SelectColumns at the end keeps only the columns the report needs. Every column you drop here is memory you never spend in the model.

#Step 6: check what you loaded

Power BI Desktop does not run outside Windows, so here is the same logic in pandas. It confirms the row counts you should see in Table view, and it measures each trap above on the real files:

import csv
import pandas as pd

folder = "olist/"
orders = pd.read_csv(folder + "olist_orders_dataset.csv", dtype=str)

# Column quality: "Empty" share, first 1000 rows (Power Query default) vs whole file
for col in ["order_approved_at", "order_delivered_customer_date"]:
    top = orders[col].head(1000).isna().mean() * 100
    full = orders[col].isna().mean() * 100
    print(f"{col:32} empty in top 1000: {top:5.2f}%   empty in all {len(orders)} rows: {full:5.2f}%")

# Products with the category translation (left outer join, then a fallback)
products = pd.read_csv(folder + "olist_products_dataset.csv", dtype={"product_id": str})
translation = pd.read_csv(folder + "product_category_name_translation.csv", encoding="utf-8-sig")
merged = products.merge(translation, on="product_category_name", how="left")
untranslated = merged[merged.product_category_name.notna() & merged.product_category_name_english.isna()]
print("\nproducts:", len(products), " no category:", products.product_category_name.isna().sum())
print("untranslated categories:", sorted(untranslated.product_category_name.unique()), "products:", len(untranslated))
merged["Category"] = (merged.product_category_name_english
                      .fillna(merged.product_category_name)
                      .fillna("unknown"))
print("distinct Category values after the fix:", merged.Category.nunique())

# Zip prefixes lost their leading zero in the source file
customers = pd.read_csv(folder + "olist_customers_dataset.csv", dtype=str)
short = customers.customer_zip_code_prefix.str.len() < 5
print("\nzip prefixes shorter than 5 characters:", short.sum(), "e.g.", customers.customer_zip_code_prefix[short].iloc[0],
      "->", customers.customer_zip_code_prefix[short].iloc[0].zfill(5))

# Reviews: quoted line breaks inside comments
with open(folder + "olist_order_reviews_dataset.csv", encoding="utf-8") as f:
    physical_lines = sum(1 for _ in f) - 1
with open(folder + "olist_order_reviews_dataset.csv", encoding="utf-8", newline="") as f:
    rows = list(csv.reader(f))[1:]
print("\nreviews: physical lines", physical_lines, " real rows", len(rows),
      " rows with a line break inside a comment", sum(any("\n" in c for c in r) for r in rows))

# A decimal-comma locale would misread prices like 58.90
items = pd.read_csv(folder + "olist_order_items_dataset.csv", dtype=str)
print("\nfirst price as text:", items.price.iloc[0], " read with en-US:", float(items.price.iloc[0]),
      " read as if '.' were a thousands separator:", float(items.price.iloc[0].replace(".", "")))

# Row counts of every table you load
for name in ["orders", "order_items", "order_payments", "order_reviews", "customers", "sellers", "products"]:
    n = len(pd.read_csv(f"{folder}olist_{name}_dataset.csv", usecols=[0]))
    print(f"{name:15} {n:>7,} rows")

order_approved_at                empty in top 1000:  0.00%   empty in all 99441 rows:  0.16%
order_delivered_customer_date    empty in top 1000:  2.50%   empty in all 99441 rows:  2.98%
 
products: 32951  no category: 610
untranslated categories: ['pc_gamer', 'portateis_cozinha_e_preparadores_de_alimentos'] products: 13
distinct Category values after the fix: 74
 
zip prefixes shorter than 5 characters: 23995 e.g. 9790 -> 09790
 
reviews: physical lines 104719  real rows 99224  rows with a line break inside a comment 3852
 
first price as text: 58.90  read with en-US: 58.9  read as if '.' were a thousands separator: 5890.0
orders           99,441 rows
order_items     112,650 rows
order_payments  103,886 rows
order_reviews    99,224 rows
customers        99,441 rows
sellers           3,095 rows
products         32,951 rows

What the output tells you:

• Profiling on 1,000 rows lied. order_approved_at looks 0% empty on the default sample but has empty values in the full file. Always switch to the entire data set before you sign off on a column.

• The translation table is incomplete. Two categories (13 products) have no English name and 610 products have no category at all. Without the fallback, a plain merge would have shown 623 products as blank in every category chart. With it, you get 74 category values: 71 translated, 2 in Portuguese, and unknown.

• 23,995 customers have a ZIP prefix with a missing leading zero. Text type plus Text.PadStart fixes them; a number type would have made them impossible to join to any geography table.

• Reviews would have broken. The file has 104,719 physical lines for 99,224 real rows. Only a CSV-aware quote style gets the right count.

• Prices are locale-sensitive. 58.90 becomes 5,890 under a decimal-comma locale, a hundredfold error that no chart would reveal.

When all queries are in place, open View, Query dependencies: OlistFolder feeds Olist Files, which feeds the function and every model query. Group the queries as in the table above and confirm that only the seven model tables are loaded.

#Step 7: move the source to SharePoint without touching the model

If the files live in a SharePoint document library, the service can refresh them without a gateway. Only Olist Files changes:

let
    Source = SharePoint.Files("https://contoso.sharepoint.com/sites/analytics", [ApiVersion = 15]),
    InFolder = Table.SelectRows(Source, each Text.EndsWith([Folder Path], "/Shared Documents/olist/")),
    CsvOnly = Table.SelectRows(InFolder, each [Extension] = ".csv")
in
    CsvOnly

Replace the site URL with your own. The function and every model query keep working, because they only ever asked Olist Files for a file by name.

#Common mistakes

• Trusting the automatic Changed Type step. It is a guess from 200 rows in your laptop's locale. Delete it and write your own type step with the data's locale.

• Profiling 1,000 rows and calling the data clean. Switch to the entire data set for the final check, as the order_approved_at result shows.

• Inner join for lookups. An inner merge with the translation table silently drops the 623 products without a match. Use left outer and handle the nulls explicitly.

• Loading staging queries. Users see tables called Olist Files or Query1, and the model carries data nobody uses. Untick Enable load.

• Hard-coded paths in eight queries. The day the folder moves, eight queries break. One parameter, one source query.

• Local files for a report that refreshes in the service. A C:\ path needs a gateway on a machine that is always on. Put shared source files in SharePoint or OneDrive for Business.

• Cleaning shared business rules in Power Query. If "valid order" means the same thing in five reports, put the rule in a SQL view or warehouse model once.

#Interview angle

#Practice task

1. Download the eight Olist files into one folder and build the OlistFolder parameter, the Olist Files staging query and fnOlistCsv.

2. Create the seven model queries with explicit types and the "en-US" locale on every numeric and date conversion.

3. Build Products with the translation merge and fallback.

4. Disable load on the parameter, the function, Olist Files and Category Translation, and put queries into Parameters, Functions, Staging and Model groups.

5. Switch profiling to the entire data set and record the empty percentage of order_delivered_customer_date.

Done when: Table view shows the seven row counts from the output above, Products[Category] has 74 distinct values, no ZIP prefix is shorter than five characters, and Query dependencies shows a single path from the parameter to each table.

#Further reading

• Microsoft Learn: data profiling tools in Power Query: the column quality, distribution and profile views and the 1,000-row default.

• Microsoft Learn: Csv.Document: every option, including QuoteStyle and Encoding.

• Microsoft Learn: data types and locale in Power Query: how conversions use the locale.

• Microsoft Learn: what is an on-premises data gateway: which sources need one.