Microsoft Certified: Power BI Data Analyst Associate flashcards

PREVIOUSYEAR is a DAX time intelligence function that returns a table that contains all dates from the previous year, given the current date context.

• Returns a full year of dates.
• Works relative to the last date in the current filter context.
• Commonly used to calculate year-over-year comparisons.
• Must be used within CALCULATE to modify the filter context of a measure.

Memory trick: Time intelligence functions are your calendar's superpowers.

A Power Query transformation that allows users to include or exclude rows from a table based on specified criteria, often improving performance by reducing data loaded.

• Reduces dataset size
• Can enable query folding for source-side filtering
• Essential for performance optimization with large datasets

Memory trick: Fast Data Loads: Filter First, Fold Smartly.

Robust type conversion in Power Query involves cleaning and transforming non-conforming data entries (e.g., text, blanks) into valid values of the target data type before applying the type conversion itself, preventing errors and ensuring data integrity.

• Clean data BEFORE changing data type.
• Handles specific non-numeric entries (e.g., 'N/A', empty strings).
• Ensures successful and accurate type conversion.

Memory trick: Clean first, then convert; avoid the data dirt.

A Power Query text transformation that removes all leading and trailing whitespace characters from text values in a column, standardizing string data.

• Removes spaces from start and end of string
• Essential for data cleaning and consistency
• Prevents matching issues and improves data quality

Memory trick: Trim the Text, Clean the Data.

A technique in Power Query, often involving the `try...otherwise` construct with `Number.FromText()`, to convert text to numeric types while gracefully handling non-numeric values by replacing them with nulls or other specified values, preventing query failures.

• Handles varied text formats and non-numeric entries.
• Uses `try ... otherwise` for error resilience.
• Preserves rows instead of generating errors or removing data.

Memory trick: When converting, always 'try' it first, and if it fails, have a 'plan B'.

A DAX function that enables an inactive relationship between two tables for the duration of a CALCULATE function's evaluation, allowing dynamic selection of relationships.

• Used when multiple relationships exist between two tables.
• Activates an inactive relationship within a CALCULATE context.
• Essential for 'role-playing dimensions'.

Memory trick: Use relationship (USERELATIONSHIP) to pick the right path.

The process of converting a text column to a numeric type in Power Query, specifically addressing inconsistent non-numeric characters (like currency symbols, varying thousand separators) that hinder direct conversion.

• Direct type conversion often fails with mixed non-numeric characters.
• Explicit 'Replace Values' steps can pre-clean the data.
• Locale settings are useful but may not cover all mixed formats.
• Order of operations (clean then convert) is crucial.

Memory trick: Clean the text first, then convert the numeric thirst.

Data unification involves standardizing disparate data elements (e.g., different IDs, naming conventions) from various sources into a consistent format during the Extract, Transform, Load (ETL) process in Power Query, before loading into the data model.

• Performed during Power Query transformations.
• Standardizes identifiers and attributes.
• Simplifies the data model (cleaner relationships).
• Improves model performance and DAX measure simplicity.

Memory trick: Standardize IDs in Power Query, keep the model clean and lean.

The ALL function in DAX is used within CALCULATE to remove all existing filters from a table or selected columns, allowing new filter contexts to be applied independently.

• Removes all filters from a table or column(s).
• Used to override existing filter context.
• Crucial for 'ignoring' slicers or other filters.
• Often combined with CALCULATE to apply new filters after removing old ones.

Memory trick: Control filters like traffic lights: ALL stops them, then a new one green-lights.

DAX filter context management involves explicitly controlling which data is visible to an expression using functions like CALCULATE, ALL, ALLEXCEPT, FILTER, KEEPFILTERS, and variables (VAR/RETURN).

• CALCULATE is fundamental for changing filter context.
• ALL removes all filters from a table/column.
• FILTER iterates a table and returns rows that meet a condition.
• VAR/RETURN helps store intermediate results and improve readability/performance.

Memory trick: Time intelligence in DAX: CALCULATE, ALL, FILTER, VAR/RETURN for precise date filtering.

Measures that calculate a value as it stood at a specific point in time, often the end of a period (e.g., month, quarter, year).

• Requires careful handling of filter context, often with ALL or ALLEXCEPT.
• Commonly used for inventory, headcounts, or active subscriptions.
• Involves comparing start/end dates to a 'snapshot date'.

Memory trick: Snapshots freeze the moment, filtering by time.

Power Query transformations that allow separating or extracting specific parts (like Date, Time, Year, Month, Day, Hour, Minute, Second) from a Date/Time column into new columns.

• Applies to columns with Date, Time, or DateTime data types.
• Common options include 'Date Only', 'Time Only', 'Year', 'Month', 'Day'.
• Found under 'Transform' tab > 'Date' or 'Time' sections.

Memory trick: DateTime columns are like combo meals; you can get just the 'Date' or just the 'Time' part.

Column Quality is a data profiling feature in Power Query that provides a quick visual summary of the data health for a selected column, showing the percentage and count of valid, error, and empty (null) values.

• Shows Valid, Error, and Empty percentages/counts.
• Provides a quick overview of data health.
• Helps identify columns with significant data quality issues.

Memory trick: Quality, Stats, Distribution: the three pillars of data insight.

The SUMX function iterates over each row of a specified table, evaluates an expression for that row, and then sums up the results of these evaluations.

• An iterator function (X-function).
• Performs calculations at a row context.
• Ideal for situations where a calculation needs to happen per row before aggregation, like weighted averages or currency conversion.

Memory trick: X-functions for eXact row calculations.

A Power Query text transformation that returns the portion of a text string that appears after a specified delimiter.

• Useful when you need the suffix of a string.
• Handles varying prefix lengths automatically.
• The delimiter itself is not included in the result.

Memory trick: Need what's AFTER? Use 'After Delimiter' to capture.

A Power Query transformation used to divide a text column into multiple new columns by specifying fixed character lengths for each segment, ideal for fixed-width data files.

• Used for fixed-width data formats
• Splits based on character count from start/end or at specific positions
• Creates new columns for each segment.

Memory trick: Fixed Width: Count Characters, Cut Precisely.

Star schema is a data modeling approach that organizes data into fact tables (containing measures) and dimension tables (containing descriptive attributes). Best practices include hiding foreign keys in fact tables and using dimension tables for filtering and slicing.

• Central fact table, surrounding dimension tables.
• One-to-many relationships from dimensions to fact.
• Hide foreign keys in fact tables.
• Use dimension attributes for filtering and visuals.

Memory trick: Hide the keys, use the dimensions, keep the star shining bright.

A Power Query authentication method used to connect to data sources (like SQL Server) by directly providing a specific username and password that are managed within the database system itself, rather than relying on Windows or cloud-based identities.

• Requires explicit username and password for the database.
• Used when Windows authentication is not applicable or preferred.
• Common for on-premises relational databases.

Memory trick: Authentication is like showing your ID; choose the right one for the gatekeeper.

The process of moving descriptive, often high-cardinality text columns from a fact table into a dedicated dimension table, replacing them with a low-cardinality integer foreign key in the fact table.

• Reduces fact table size and improves compression.
• Enhances query performance for descriptive attributes.
• Standard star schema design principle.
• Applies to 'junk dimensions' or new specific dimensions.

Memory trick: Big fact table columns? Dimension them out!

A Power Query connector used to import and combine data from multiple files within a specified folder, assuming they have a consistent structure.

• Automates combining files from a directory.
• Files must typically have the same structure and headers.
• Can handle various file types (CSV, Excel, JSON, etc.) within the folder.

Memory trick: Many files in a folder, use the Folder connector to unite them all.

The process in Power Query of creating new columns that isolate either the date part or the time part from an existing DateTime column.

• Requires the source column to be of DateTime data type.
• Power Query has dedicated UI options for this under 'Add Column'.
• Ensures precise separation of date and time components.
• Results in new columns without modifying the original.

Memory trick: From DateTime, 'Add Column' makes Date and Time fly.

A Power Query text transformation that removes all leading and trailing whitespace characters from text values in a column.

• Standardizes text by removing outer spaces.
• Does not affect spaces within the text string.
• Crucial for accurate grouping and filtering.

Memory trick: To clean up the ends, 'Trim' is your friend.

Measures can be directly referenced by their names within other DAX measures. This allows for modularity and reusability, ensuring that the referenced measure's logic and context transitions are inherited.

• Simplifies complex calculations.
• Ensures consistent logic across measures.
• Automatically respects filter context.

Memory trick: Combine existing pieces, don't rebuild them from scratch.

A Power Query transformation that divides a single text column into multiple new columns based on a specified delimiter, allowing for easy parsing of structured strings.

• Creates new columns from one existing column
• Uses characters (e.g., comma, hyphen) as separation points
• Useful for parsing IDs, addresses, or concatenated values

Memory trick: Split Columns: Divide and Conquer the Data.

Filter propagation in DAX refers to how filters applied to one table travel through relationships to other tables in the data model. This is a key mechanism for how filter context is established and modified in Power BI.

• Filters propagate along one-to-many relationships from the 'one' side to the 'many' side.
• CALCULATE can modify filter context, including adding new filters that propagate.
• Optimized filter propagation is crucial for measure performance.
• Understanding relationship direction is vital for effective filtering.

Memory trick: Filter once, propagate smart, count distinct with a clear path.

A Left Outer Join in Power Query combines two tables by including all rows from the first (left) table and only the matching rows from the second (right) table. If no match is found for a left row, the columns from the right table will contain null values.

• Returns all rows from the left table.
• Returns matching rows from the right table.
• Pads non-matching right rows with nulls.

Memory trick: Left keeps all left, Right keeps all right, Inner only common, Outer keeps all.

A Power BI data connectivity mode where data is loaded into the Power BI Desktop file (PBIX) and stored in the Power BI model. This allows for full Power Query and DAX capabilities.

• Data is cached in Power BI Desktop/Service.
• Offers best performance for reports.
• Supports full Power Query transformations and DAX.
• Requires data refresh for up-to-date data.

Memory trick: Import for speed, DirectQuery for live, Live for cubes, OData for feeds.

Using the CALCULATE function with ALL() to remove existing filters from a table or column, allowing a new, specific filter to be applied without interference from the original filter context.

• CALCULATE modifies filter context.
• ALL() removes filters from a table or column.
• Allows creating measures that are independent of external filters for specific dimensions.

Memory trick: All filters out, then specific filter in.

A DAX function that returns a delimited text string representing the path from the oldest ancestor to a specified item in a parent-child hierarchy.

• Essential for parent-child hierarchy analysis.
• Creates the textual representation of the hierarchy.
• Used in conjunction with other PATH functions (PATHCONTAINS, PATHITEM) for calculations.

Memory trick: Path to the manager, then count the team.

Optimizing fact table columns involves ensuring only essential foreign keys and measures are present, minimizing cardinality and data types that consume excessive memory.

• Fact tables should be 'skinny' – few columns, many rows.
• Avoid descriptive text columns in fact tables; move them to dimensions.
• High cardinality columns increase model size and query time.
• Foreign keys (integer IDs) and measures (numeric values) are appropriate for fact tables.

Memory trick: Fact tables are like lean machines: only essential parts, no extra fluff.

A DAX function that removes all context filters from a table except for those filters that have been applied to the specified columns.

• Used within CALCULATE to modify filter context.
• Preserves filters on specified columns.
• Removes all other filters from the table.

Memory trick: Always Except the ones you want to keep.

Row-level currency conversion involves applying the correct exchange rate to each individual transaction amount based on its specific date and currency, ensuring accuracy in aggregated totals.

• Requires iterating through each transaction row.
• Needs a way to lookup the correct exchange rate for each row.
• Exchange rates are typically date-sensitive.
• Essential for accurate reporting in multi-currency environments.

Memory trick: Convert currencies row-by-row, like a global money changer.

A dimension hierarchy organizes related columns into a logical drill-down path. It optimizes how Power BI processes queries, especially for high-cardinality attributes, by allowing aggregation at higher levels first.

• Improves user experience for navigation.
• Enhances query performance for drill-down scenarios.
• Defined within the dimension table in the model view.

Memory trick: Too many unique things slow you down, organize them into a ladder.

DAX time intelligence functions are used to perform date-related calculations such as year-to-date, previous year, or rolling averages. For rolling windows, functions like DATESBETWEEN or DATESINPERIOD are combined with CALCULATE to define flexible date ranges for aggregation.

• Requires a marked Date table.
• CALCULATE is essential for changing date-based filter context.
• DATESBETWEEN defines a custom date range.
• LASTDATE or ENDOFMONTH helps define the anchor point for the window.

Memory trick: Rolling windows: Anchor, range, then CALCULATE the count.

The 'Change Type With Locale' transformation in Power Query allows you to convert a column to a specific data type while explicitly defining the cultural formatting rules (locale) for numbers, dates, or times. This is crucial for correctly interpreting regional differences in decimal separators, thousands separators, and date formats.

• Converts column to specified data type.
• Applies specific cultural formatting rules (locale).
• Handles regional variations in numbers and dates (e.g., '.' vs ',' for decimals).

Memory trick: Locale is the key to understanding global numbers.

Strategies to mitigate the performance impact of columns with many unique values in a Power BI data model.

• High cardinality increases memory consumption.
• Can slow down filter propagation and query execution.
• Remove unused high-cardinality columns.
• Consider archiving or moving to separate tables if needed for drill-through.

Memory trick: Cardinality too high? Prune and simplify.

The CALCULATE function evaluates an expression in a context modified by new filters. It is one of the most powerful and frequently used functions in DAX.

• Changes filter context for an expression.
• First argument is an expression (e.g., SUM, AVERAGE).
• Subsequent arguments are filters or context modifiers.
• Filters applied directly to related tables will propagate.

Memory trick: CALCULATE is the filter controller, always changing the view.

Optimizing a fact table involves minimizing its size and complexity by including only foreign keys and measures, while offloading descriptive attributes to dimension tables.

• Fact tables store quantitative data (measures) and foreign keys.
• Avoid descriptive text columns in fact tables to reduce size.
• High cardinality columns, especially text, in fact tables negatively impact performance.
• Moving descriptive attributes to dimension tables improves query performance and data model efficiency.

Memory trick: Optimize your model like a race car: shed weight and streamline parts.

The process of converting all text values in a column to a consistent letter case (e.g., all uppercase or all lowercase) to ensure proper grouping, filtering, and matching.

• Crucial for consistent unique identifiers and categorical data.
• Uppercase or Lowercase transformations are commonly used.
• Prevents different casings from being treated as distinct values.

Memory trick: For consistent IDs, choose one case and abide.

Append Queries in Power Query combines two or more tables by stacking their rows on top of each other. This operation requires the tables to have compatible column structures (same column names and data types).

• Combines rows from multiple tables vertically.
• Requires compatible column structures.
• Useful for consolidating data from similar sources (e.g., yearly sales files).

Memory trick: Append stacks, Merge joins, Group aggregates.

The Power Query Excel Workbook connector allows you to select and import multiple sheets, named ranges, or tables from a single Excel file. The Navigator pane enables multi-selection to create separate queries for each chosen item, facilitating flexible data preparation.

• Connects to .xlsx, .xlsm, .xlsb files.
• Navigator shows sheets, named ranges, and tables.
• Allows multi-selection to create multiple queries efficiently.

Memory trick: The Excel Navigator is like a table of contents; pick all the chapters you need at once.

A setting in Power BI Desktop that designates a specific table as the primary date table for the model, enabling optimized time intelligence calculations.

• Crucial for DAX time intelligence function performance.
• Requires a continuous range of unique dates.
• Allows VertiPaq to apply specific optimizations for date filters.

Memory trick: Mark your dates for speed, don't complicate the schema.

Dual storage mode in Power BI allows tables to operate in either DirectQuery or Import mode depending on the query, providing a balance of performance and data freshness.

• Combines benefits of Import and DirectQuery.
• Optimizes for both performance and data freshness.
• Tables can dynamically switch modes based on query context.

Memory trick: Import for speed, Query for fresh, Dual for the best of both.

A set of Power Query transformations (Uppercase, Lowercase, Capitalize Each Word) used to ensure consistency in text data, which is crucial for accurate filtering, grouping, and merging.

• Prevents issues caused by case-sensitive comparisons.
• Common options: Uppercase, Lowercase, Capitalize Each Word.
• Applied via the 'Transform' tab in Power Query Editor.

Memory trick: Casing is like clothing; everyone needs to wear the same uniform for consistency.

Optimized integer data types in Power BI (e.g., Whole Number, which can map to Int64 or Int32) are chosen to store whole numbers efficiently. Selecting the smallest appropriate type reduces model size and improves performance, while ensuring the type can accommodate the full range of values.

• Reduces model size and improves performance.
• Int32: stores up to ~2 billion.
• Int64 (Whole Number): stores up to ~9 quintillion.
• Choose smallest type that safely accommodates max value.

Memory trick: Size matters for speed, but safety first.

Calculating measures based on the absolute latest date in the entire dataset, ignoring external date filters, requires using ALL() or ALLEXCEPT() within a CALCULATE to determine the maximum date before applying time intelligence functions.

• Bypasses external filter contexts for date determination.
• Ensures consistent 'latest date' reference.
• Often involves nested CALCULATE with ALL().

Memory trick: To find the true end of time, you must ignore all distractions.

DAX provides a set of specific functions (PATH, PATHITEM, PATHLENGTH, PATHCONTAINS) to effectively manage and query data organized in parent-child hierarchies within a single table.

• PATH creates a delimited text string representing the path from the root to a specific item.
• PATHITEM extracts a specific item from a path at a given position.
• PATHLENGTH returns the number of items in a path.
• PATHCONTAINS checks if a specific item exists within a path.

Memory trick: Navigating a hierarchy is like finding your way through a family tree with a map.

A cumulative sum, or running total, is a measure that aggregates values sequentially over a specified dimension, typically time, accumulating the total as new data points are added.

• Aggregates values from the beginning of a period up to the current point.
• Often requires modifying filter context to include past periods.
• Can be challenging to implement while respecting other dimensions (e.g., customer, product).
• ALLSELECTED is useful for running totals that should respect external filters but ignore internal table filters.

Memory trick: Running totals are like a marathon: keep adding to the distance covered.

The 'Choose Columns' transformation explicitly selects which columns to retain. When applied to foldable data sources (like SQL databases), Power Query can translate this operation into a native query (e.g., a SQL SELECT statement), reducing data transferred and improving performance through 'query folding'.

• Selects a subset of columns to keep.
• Directly supports query folding for relational databases.
• Reduces data volume fetched from source.

Memory trick: Folding queries is like sending a precise shopping list to the database, not buying the whole store.

Calculated columns store values for each row in the model and are computed during data refresh, consuming memory. Measures are calculated on-the-fly at query time based on the current filter context and do not consume memory for storage.

• Calculated columns: row-level, stored in model, refreshed with data.
• Measures: aggregated, calculated at query time, context-dependent.
• Use measures for aggregations to optimize performance.
• Use calculated columns for static row-level attributes (e.g., age from birthdate).

Memory trick: Optimize by thinking 'measure first' for aggregations, not 'column always'.

A Power Query transformation used to flatten a column containing structured record values (like those from JSON or nested tables) into new columns, exposing the fields within the record.

• Used for nested data structures (JSON, records)
• Converts a single record column into multiple new columns
• Essential for flattening hierarchical data

Memory trick: Expand Records: Unpack the Nested Tree into a Flat Table.

A DAX iterator function that evaluates an expression for each row of a table and then sums the resulting values.

• Syntax: SUMX(<table>, <expression>).
• Essential for calculations that need to happen at the row level before aggregation (e.g., Price * Quantity).
• Creates its own row context for the expression.

Memory trick: SUMX iterates, calculates, then sums.

DATESINPERIOD is a DAX time intelligence function that returns a table that contains a column of dates starting with a given start date and continuing for the specified number of intervals.

• Used for defining dynamic date ranges.
• Essential for rolling calculations like moving averages.
• Requires a start date, number of intervals, and interval type (e.g., DAY, MONTH, YEAR).
• Returns a table of dates that can be used within CALCULATE.

Memory trick: Time intelligence functions are like a calendar, helping you navigate through dates.

Power Query transformations that allow extracting specific components (e.g., Year, Month, Day, Week of Year) from a column with a 'Date' or 'DateTime' data type into new, separate columns.

• Applies to Date or DateTime data types.
• Found under 'Transform' tab > 'Date' group.
• Common extractions include Year, Month, Day, Day of Week, Week of Year.

Memory trick: Dates are like LEGOs; you can pull out just the 'Year' block.

Applying date-based filters within a DAX measure that adjust based on a dynamic 'current date' (e.g., TODAY() or a selected date in a slicer).

• Uses CALCULATE and FILTER functions.
• Compares a date column to a dynamic date expression (e.g., TODAY() - 90).
• Ensures calculations reflect the current context or system date.

Memory trick: Today's date minus time, then filter and calculate.

The process of isolating specific components (like year, month, day) from a date or datetime column in Power Query for analytical purposes.

• Requires the column to be of a Date or DateTime data type.
• Power Query provides built-in transformations for common date parts.
• Ensures accurate extraction, unlike text manipulation.

Memory trick: Date must be a date, then extract the part you crave.

A Power Query transformation that allows you to navigate into a structured column (containing records, lists, or tables) and select its internal fields or elements to be promoted as new top-level columns in the current table.

• Used for nested data structures (records, lists, tables).
• Promotes internal fields to new columns.
• Crucial for flattening complex data sources like JSON/XML.

Memory trick: Expand is like opening a box and taking out what's inside to put it on the shelf.

The 'Expand Record' transformation in Power Query flattens a column containing records (nested objects) into new columns, allowing access to the individual fields within each record.

• Used for nested JSON objects or structured data.
• Converts a record column into multiple columns.
• Makes nested data accessible for reporting.

Memory trick: Expand the box to see all the items inside.

A Mixed Storage Mode strategy in Power BI involves using different data connectivity modes (e.g., DirectQuery, Import) for different tables or parts of a data model, optimizing for diverse requirements such as real-time freshness for operational data and performance for historical analytical data.

• Combines DirectQuery and Import modes.
• Balances real-time needs with query performance.
• Tailors connectivity to specific data source characteristics and reporting needs.

Memory trick: Fresh for fast, Stored for deep, both make the insights leap.

A Power Query transformation that converts a column's data type while considering cultural formatting rules. This is particularly useful for parsing dates, times, and numbers that vary by region.

• Handles varied date, time, and number formats.
• Leverages cultural settings for parsing.
• Accessed via 'Using Locale...' option in Change Type menu.

Memory trick: Dates are global travelers; locales help them fit in anywhere.