Power Query p1

• Power BI Desktop
• 1.5 hours

Prerequisites: Star schemas (d6)

Load the star into Power BI with Power Query: one function for the eight files critwire publishes, a fixed base URL the Power BI Service can refresh, and types set with an explicit culture so the load reads the same on any computer. Power BI Desktop is Windows-only (the setup page); the files are the star Star schemas, d6 builds, under the lab data's star/, drawn on the star schema.

Concept

Get the star into Power BI so it loads the same way on any computer and refreshes in the Power BI Service. Power Query is the tool inside Power BI Desktop (and Excel) that fetches and shapes data before it reaches the model. It records each step as a line of M, its formula language, so a load is code you can read, copy and review. Four rules make a load that works anywhere:

• A fixed base URL. Web.Contents fetches the file. Give it a fixed base URL and put the file name in RelativePath: the Power BI Service can then refresh the published report on a schedule with anonymous access. A whole URL built by joining text is a dynamic data source, which the Service won't refresh.
• Say how the file is written. Csv.Document parses it with UTF-8 (Encoding = 65001) and CSV quoting stated, so accents and commas inside quotes are read as written; Table.PromoteHeaders takes the first row as column names.
• Types with an explicit culture. Table.TransformColumnTypes sets each column's type. A culture (a locale, such as "en-US") says how text becomes a number or a date. Without one, Power Query uses the computer's own: on a computer set to German, the dot is a thousands separator, so 179.18 becomes 17,918. critwire's files write numbers with a dot, so every load states "en-US".
• Codes are text. country_code holds NA (Namibia), which other tools read as "not available", and keys like dataset_key are identifiers, never numbers to add. An empty cell in a numeric column becomes null, Power Query's missing value; an empty cell in a text column stays an empty text, "".

One function serves all eight tables, so the rules are written once. A custom function in M is a query whose body is (parameters) => expression; other queries call it by name.

Worked example

The function, then the largest table. Create LoadStar as a blank query (Home → Get data → Blank query → Advanced editor), paste the block, and rename the query LoadStar. The first time it runs, Power BI asks how to connect to critwire.com: choose Anonymous, privacy level Public. The base URL is fixed and the file name goes in RelativePath, so the Service sees one data source, https://critwire.com, for all eight tables.

(file as text, types as list) as table =>
let
    Source = Web.Contents("https://critwire.com", [RelativePath = "learn/data/star/" & file & ".csv"]),
    Rows = Csv.Document(Source, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Rows, [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(Promoted, types, "en-US")
in
    Typed

types is a list of pairs, a column's name and its type, in the form Table.TransformColumnTypes takes. Each table is then one call. Create a blank query, name it fact_records, and paste this; whole numbers are Int64.Type, decimals type number:

LoadStar("fact_records", {
    {"record_id", Int64.Type}, {"species_id", Int64.Type}, {"country_code", type text},
    {"dataset_key", type text}, {"year", Int64.Type}, {"month", Int64.Type},
    {"basis_of_record", type text}, {"license", type text}, {"longitude", type number},
    {"latitude", type number}, {"coordinate_uncertainty_m", type number}
})

A record with no month, or no stated uncertainty, has an empty cell, which becomes null. Power Query shows each step on the right (Applied steps); the function's steps run inside the call, so fact_records shows one.

Lab

1. The other seven tables. Each is a blank query named after its file, calling LoadStar with the file's columns. The manifest gives each column's type, and each has one M type:

   Manifest type	M type	Power BI calls it
   INTEGER, BIGINT	Int64.Type	Whole number
   DOUBLE	type number	Decimal number
   DECIMAL(12,2)	Currency.Type	Fixed decimal number
   VARCHAR	type text	Text

   So dim_country = LoadStar("dim_country", {{"country_code", type text}, {"name", type text}, {"continent", type text}, {"region", type text}, {"subregion", type text}}), and dim_year = LoadStar("dim_year", {{"year", Int64.Type}, {"decade", Int64.Type}, {"label", type text}}). In dim_species, dim_dataset, fact_species_country and dim_habitat_class, the ids and codes that the manifest calls INTEGER are Int64.Type and the rest is text. fact_country_habitat's est_area_km2 is Currency.Type: a fixed decimal number keeps four decimal places exactly, so 5,578 areas with two decimals add up to the cent, where a decimal number (a binary double) can leave a trailing digit.

2. Close & Apply (Home). Power BI loads the eight tables into the model; LoadStar is a function, so it isn't loaded. In Table view, the status bar shows each table's rows: compare them with the manifest's rows.

3. Look at the edges. In dim_country, filter country_code to NA: Namibia, a text code, not a missing value. In dim_year, the row -1 is the unknown member (d6), with decade null and label Unknown. In fact_records, filter year to -1, then month to null.

4. Refresh. Home → Refresh repeats every query from critwire.com: the same tables until the files change. To share the report, Home → Publish to a workspace; then, in the Service, the semantic model's Settings → Data source credentials → Edit credentials: Anonymous, privacy level Public, and set a scheduled refresh (a Pro license allows eight a day). A dynamic data source is where this stops: the settings page says the model can't be refreshed.

5. Your own database instead. Power BI also reads PostgreSQL directly (Get data → PostgreSQL database): server localhost, database learn, with the learn_powerbi login of Roles and privileges, d5, and the navigator lists learn_curriculum's tables. Power BI tries an encrypted connection first; a server on your own computer without TLS makes it ask whether to connect unencrypted, which is fine for localhost. That suits a database only you use. A report in the Service that refreshes from a database on your computer needs an on-premises data gateway, a Windows service on that computer, running whenever the refresh is due; files on a public URL need none, which is why the course reads the star from critwire.com.

Result

Eight tables, with the manifest's rows: dim_species 76, dim_country 251, dim_dataset 400, dim_year 177, fact_records 40,395, fact_species_country 472, dim_habitat_class 75 and fact_country_habitat 5,578 (246 countries). In fact_records, 2,289 records have year -1 (unknown) and none has country ZZ; 6,545 have no month and 24,002 no stated uncertainty, all null; 176 are from Namibia, NA. Refresh repeats the load from critwire.com and gives the same tables until the files change.

Exercise

A load check in M. Load dim_habitat_class and fact_country_habitat with LoadStar, and return one small table to compare with the manifest: each table's rows, the area rows with an empty key, the area rows whose class isn't in dim_habitat_class, the total estimated area, and Namibia's. Which type keeps the area's sums exact?

let
    Classes = LoadStar("dim_habitat_class", {{"habitat_code", Int64.Type}, {"name", type text}, {"level1_code", Int64.Type}, {"level1_name", type text}}),
    Areas = LoadStar("fact_country_habitat", {{"country_code", type text}, {"habitat_code", Int64.Type}, {"est_area_km2", Currency.Type}}),
    Check = #table({"check", "value"}, {
        {"dim_habitat_class rows", Table.RowCount(Classes)},
        {"fact_country_habitat rows", Table.RowCount(Areas)},
        {"area rows with an empty key", Table.RowCount(Table.SelectRows(Areas, each [country_code] = null or [country_code] = "" or [habitat_code] = null))},
        {"area rows with no class", Table.RowCount(Table.NestedJoin(Areas, {"habitat_code"}, Classes, {"habitat_code"}, "class", JoinKind.LeftAnti))},
        {"total km2", List.Sum(Areas[est_area_km2])},
        {"Namibia (NA) km2", List.Sum(Table.SelectRows(Areas, each [country_code] = "NA")[est_area_km2])}
    })
in
    Check

Check

The answer: Currency.Type (fixed decimal number) for est_area_km2. Table.NestedJoin with JoinKind.LeftAnti keeps the rows of the first table that have no match in the second, so its row count is the number of orphans. A text key is checked for both null and "".

75 and 5,578 rows, as the manifest says; no empty key and no area without its class; 146,579,613.97 km² in all, and 822,099.38 km² for Namibia, whose 33 rows kept their code. Change LoadStar's culture to "de-DE" and run it again: every area is read with the dot as a thousands separator, and the totals no longer match. The areas are estimates from a habitat map, not measurements.

Dialects

• Excel (Windows): the same Power Query and the same M. Data → Get Data → From Other Sources → Blank Query, then the same LoadStar and calls; Data → Refresh All repeats them. A table loaded to a sheet stops at 1,048,576 rows, so fact_records fits; load to the Data Model for more.
• Tableau: Tableau Desktop's Text file connector reads files on your computer, so download the star first. Set each code's type to String on the Data Source page, and relate the tables there by their keys.
• pandas: pd.read_csv(url, dtype={"country_code": str}, keep_default_na=False, na_values=[""]). pandas reads NA as missing by default; keep_default_na=False stops that, and na_values=[""] keeps empty cells missing. Python's float already reads 12.5 the same way in every locale.
• SQL: DuckDB's read_csv('https://critwire.com/learn/data/star/dim_country.csv', types = {'country_code': 'VARCHAR'}) (the httpfs extension fetches the URL); PostgreSQL's \copy dim_country FROM 'dim_country.csv' WITH (FORMAT csv, HEADER) into a table d6's block made, which reads an empty unquoted cell as NULL and NA as text. PostgreSQL reads only the dot as a decimal point; DuckDB does too unless told otherwise (decimal_separator = ',').

Mark done