1. A data engineer is integrating data from a legacy system into Power BI. The system exports sales data in a fixed-width text file where each field occupies a specific number of characters, without delimiters. For example, a row might look like '20240101CUST001 PRODXYZ1234.56'. The engineer needs to extract '20240101' (Date), 'CUST001' (CustomerID), 'PRODXYZ' (ProductID), and '1234.56' (Amount) into separate columns. Which Power Query transformation is most suitable for this task?
Prepare the data
A.Split Column by Delimiter
B.Split Column by Number of Characters
C.Extract Text After Delimiter
D.Parse JSON
Show answerAnswer
B. Split Column by Number of Characters
Fixed-width text files are characterized by fields occupying a specific number of characters, not by delimiters. 'Split Column by Number of Characters' is precisely designed for this scenario, allowing you to define break points based on character positions to extract each field into its own column. The other options are for delimited or structured data.
2. A data modeler is optimizing a Power BI model. The 'Sales' table has millions of rows and includes columns like 'OrderID', 'ProductID', 'CustomerID', and 'SalesAmount'. The 'ProductID' column is an integer. The modeler notices that many visuals use 'ProductID' directly as a filter or in row/column headers, even though a 'Products' dimension table exists with 'ProductID' and 'ProductName'. The 'Products' table has a one-to-many relationship to the 'Sales' table. Which optimization technique should the modeler apply to improve performance and user experience?
Model the data
A.Change the data type of 'ProductID' in the 'Sales' table to text.
B.Create a calculated column in the 'Sales' table combining 'ProductID' and 'ProductName'.
C.Establish a bidirectional relationship between 'Products' and 'Sales' tables.
D.Hide the 'ProductID' column in the 'Sales' table from report view.
Show answerAnswer
D. Hide the 'ProductID' column in the 'Sales' table from report view.
Hiding the 'ProductID' column in the 'Sales' table (the fact table) from the report view encourages users to use the 'ProductID' or 'ProductName' from the 'Products' dimension table instead. This is a best practice in star schema design, as filtering through a dimension table is more efficient and provides better user experience by allowing filters on attributes like 'ProductName' directly, while the underlying relationship with the numerical 'ProductID' handles the filtering of the fact table.
3. A data analyst is connecting to an on-premises SQL Server database to retrieve sales data. The database is secured, and the analyst needs to provide specific credentials (username and password) to access the data. Which Power Query authentication method should the analyst choose to connect to this database?
Prepare the data
A.Organizational account
B.Database
C.Anonymous
D.Windows
Show answerAnswer
B. Database
The 'Database' authentication method allows you to directly enter a specific username and password for the SQL Server database, which is required when Windows authentication is not being used or is insufficient for the specific database user.
4. A data modeler is optimizing a Power BI model for an inventory management system. The model has a 'InventoryTransactions' fact table and a 'Products' dimension table. To improve query performance, the modeler wants to reduce the number of distinct values in certain columns within the 'InventoryTransactions' table without losing critical information. For example, a 'TransactionDescription' column has many unique text values but often describes similar types of transactions. What is the most effective data modeling technique to address this specific issue?
Model the data
A.Convert the 'TransactionDescription' column to a calculated column that uses a shorter text string.
B.Create a new dimension table for 'Transaction Descriptions' and replace the original column with a foreign key.
C.Remove the 'TransactionDescription' column entirely from the fact table.
D.Change the data type of 'TransactionDescription' to a fixed-length text type.
Show answerAnswer
B. Create a new dimension table for 'Transaction Descriptions' and replace the original column with a foreign key.
This scenario describes creating a new dimension table, often called a 'junk dimension' or simply a new dimension. By extracting the 'TransactionDescription' into its own dimension table and linking it back to the 'InventoryTransactions' fact table with a foreign key, you replace a high-cardinality text column in the fact table with a low-cardinality integer key, significantly reducing the fact table's size and improving compression and query performance.
5. A Power BI developer is importing data from a folder containing multiple CSV files, each representing sales data for a different region. All CSV files have the same structure and column headers. The developer needs to combine all these files into a single table in Power Query. Which option in the 'Get Data' interface is specifically designed for this scenario?
Prepare the data
A.Web
B.Folder
C.Text/CSV
D.SQL Server database
Show answerAnswer
B. Folder
The 'Folder' connector in Power Query is specifically designed to combine multiple files with the same structure from a specified directory. It automates the process of loading and appending data from each file into a single table.
6. A data modeler is preparing a fact table in Power Query from a transactional system. The table contains a 'SalesDateTime' column. For reporting purposes, the modeler needs to create a separate 'Date' column containing only the date part (without time) and a 'Time' column containing only the time part (without date). These new columns should be derived from 'SalesDateTime' and added to the table. Which Power Query transformation approach is most suitable?
Prepare the data
A.Add Custom Column using `Date.From([SalesDateTime])` and another Custom Column using `Time.From([SalesDateTime])`.
B.Split Column by Delimiter (space), then change type for each new column.
C.Duplicate 'SalesDateTime' twice, then for one duplicate change type to 'Date', and for the other, change type to 'Time'.
D.Use 'Add Column > Date > Date Only' and 'Add Column > Time > Time Only'.
Show answerAnswer
D. Use 'Add Column > Date > Date Only' and 'Add Column > Time > Time Only'.
Power Query offers direct, user-friendly options under 'Add Column' to extract 'Date Only' and 'Time Only' components from a DateTime column. This is the most straightforward and efficient method compared to manual duplication, custom M functions, or splitting text.
7. A data modeler is optimizing a Power BI model for a large manufacturing company. The model includes a 'ProductionLog' fact table with billions of rows, containing granular event data. This table has a 'Timestamp' column (datetime) and several high-cardinality text columns used for detailed logging. The model is experiencing slow refresh times and report performance. Which optimization technique specifically targets improving performance related to high-cardinality text columns in a fact table?
Model the data
A.Increase the 'Data Cache Maximum Size' setting in Power BI Desktop options.
B.Convert the 'Timestamp' column to a calculated column with only the date part.
C.Change the storage mode of the 'ProductionLog' table to DirectQuery.
D.Replace high-cardinality text columns with integer keys referencing a new dimension table.
Show answerAnswer
D. Replace high-cardinality text columns with integer keys referencing a new dimension table.
High-cardinality text columns in a fact table consume significant memory and can severely impact query performance due to inefficient compression and larger data volumes. Replacing these text columns with integer keys that link to a new, smaller dimension table containing the actual text values (a process called 'dimensioning' or 'star schema optimization') is a fundamental and highly effective optimization for fact tables.
8. A data analyst has a table in Power Query with a 'ProductCategory' column containing values like 'Electronics', 'Clothing ', ' Home Goods', 'Books'. The column has inconsistent leading and trailing spaces. Before loading the data into the model, the analyst needs to remove these extra spaces to ensure consistent categorization. Which Power Query transformation should be applied?
Prepare the data
A.Split Column
B.Replace Values
C.Format > Trim
D.Format > Clean
Show answerAnswer
C. Format > Trim
The 'Trim' transformation in Power Query specifically removes leading and trailing whitespace characters from text strings, which is exactly what is needed to standardize the 'ProductCategory' column.
9. A data analyst is designing a Power BI data model for a global retail company. The model needs to support complex financial reporting, including the calculation of 'Gross Profit' which is defined as 'Sales Amount' minus 'Cost of Goods Sold'. Both 'Sales Amount' and 'Cost of Goods Sold' are measures already defined in the model. The analyst wants to ensure that the 'Gross Profit' calculation is dynamic and responds correctly to all filters applied to the report. Which of the following DAX expressions should the analyst use to calculate 'Gross Profit'?
Model the data
A.Gross Profit = [Sales Amount] - [Cost of Goods Sold]
A. Gross Profit = [Sales Amount] - [Cost of Goods Sold]
When measures are already defined, they can be directly referenced within other measures using their names. This approach ensures that the underlying logic of 'Sales Amount' and 'Cost of Goods Sold' measures, including any implicit or explicit filter contexts, is correctly applied to the 'Gross Profit' calculation.
10. A data analyst is importing data from a CSV file into Power BI. The CSV file contains a column named `EmployeeID` with values like 'EMP-001', 'EMP-002', 'EMP-003'. The analyst needs to ensure that only the numeric part of the ID (e.g., '001', '002', '003') is loaded into the data model and stored as an integer. Which Power Query transformation should be used to achieve this?
Prepare the data
A.Replace Values
B.Extract Text Before Delimiter
C.Extract Text Between Delimiters
D.Split Column by Delimiter
Show answerAnswer
D. Split Column by Delimiter
Splitting the column by delimiter '-' will create new columns. The second column will contain the numeric part ('001', '002'). This is a direct and efficient way to separate the components of the ID. Renaming the new column and changing its type to integer would be the subsequent steps.
11. A data modeler is working on a Power BI model that contains a 'Customers' table, a 'Products' table, and a 'Sales' fact table. The 'Sales' table has millions of rows. The modeler needs to create a measure to calculate the count of distinct customers who purchased a specific product, 'Product Z'. The report is experiencing slow performance when this measure is used. Which DAX expression would be most efficient for this calculation?
D. CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), Products[ProductName] = "Product Z")
The most efficient way to achieve this is by using CALCULATE with a direct filter on the related 'Products' table. 'CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), Products[ProductName] = "Product Z")' directly applies a filter to the 'Products' table. Due to the one-to-many relationship from 'Products' to 'Sales', this filter propagates to the 'Sales' table, effectively filtering it to only include sales of 'Product Z'. Then, 'DISTINCTCOUNT(Sales[CustomerID])' counts the unique customers within this filtered context. This avoids expensive table iterations or lookups within the measure.
12. A data analyst is combining two tables in Power Query: 'Sales' (containing daily sales transactions) and 'Products' (containing product details like category and price). Both tables have a 'ProductID' column. The requirement is to include all sales transactions, even those with a 'ProductID' that does not exist in the 'Products' table, and to show product details where they are available. Which type of join should be used?
Prepare the data
A.Inner Join
B.Right Outer Join
C.Left Outer Join
D.Left Anti Join
Show answerAnswer
C. Left Outer Join
A Left Outer Join includes all rows from the first (left) table and the matching rows from the second (right) table. If there's no match, the columns from the right table will have nulls. This perfectly matches the requirement to include all sales transactions (from the left 'Sales' table) and product details where available.
13. You are connecting Power BI to an Azure SQL Database. The database contains sensitive customer information, and you have been granted read-only access to specific tables. To ensure data privacy and compliance, you need to use a secure connection method that encrypts data in transit. Which data connectivity mode in Power BI Desktop should you choose to meet these requirements while allowing for scheduled refreshes?
Prepare the data
A.Connect using OData Feed
B.Import
C.DirectQuery
D.Live Connection
Show answerAnswer
B. Import
The Import mode is the most common and flexible option, copying data into the Power BI model. It allows for full Power Query transformations, DAX calculations, and scheduled refreshes. When connecting to Azure SQL Database, the connection is encrypted by default, satisfying the security requirement.
14. A data analyst is creating a Power BI report to track product sales. The model includes a 'Sales' table and a 'Products' table. The 'Products' table has a 'ProductCategory' column. The analyst needs to create a measure that calculates the sales amount for a specific product category, say 'Electronics', and always displays this value regardless of any filters applied to the 'Products' table in the report. Which DAX expression achieves this?
A. CALCULATE(SUM(Sales[SalesAmount]), ALL(Products), Products[ProductCategory] = "Electronics")
To calculate sales for a specific category while ignoring all other filters on the 'Products' table, you must use ALL(Products) to remove existing filters from the entire table, and then apply the specific filter for 'Electronics'.
15. A data analyst is integrating data from a legacy system into Power BI. The system exports transaction data into a series of CSV files, one for each month. Each CSV file has the same schema, and contains columns like 'TransactionID', 'TransactionDate', and 'Amount'. The analyst needs to combine all these monthly CSV files into a single table in Power Query. Which Power Query connector and subsequent action are most appropriate for this task?
Prepare the data
A.Folder connector, then Combine & Transform Data.
B.Text/CSV connector for each file, then Append Queries.
C.SQL Server connector to the file system, then UNION ALL.
D.Web connector to a shared network drive, then Parse CSV.
Show answerAnswer
A. Folder connector, then Combine & Transform Data.
The Folder connector is specifically designed to access and combine multiple files within a directory, and its 'Combine & Transform Data' feature automates the process of loading and appending data from files with the same schema.
16. You are building a Power BI report for a sales team. You need to combine data from an Excel workbook containing monthly sales figures and a SQL Server database table that stores customer demographics. Both sources have a common column, `CustomerID`, which uniquely identifies each customer. You want to ensure that all sales records are included, even if there is no corresponding customer demographic information. Which Power Query join kind should you use?
Prepare the data
A.Right Outer (all from second, matching from first)
B.Full Outer (all rows from both)
C.Left Outer (all from first, matching from second)
D.Inner (only matching rows)
Show answerAnswer
C. Left Outer (all from first, matching from second)
To include all records from the primary sales table (the 'first' table) and only the matching records from the customer demographics table (the 'second' table), a Left Outer join is the appropriate choice. This ensures no sales data is lost.
17. A data modeler is building a Power BI model for an HR department. The model includes an 'Employees' table with 'EmployeeID', 'ManagerID', and 'Department'. The 'ManagerID' column refers to 'EmployeeID' within the same table, creating a parent-child hierarchy. The HR department needs to calculate the total number of employees reporting to each manager, including indirect reports (employees reporting to subordinates of a manager). Which DAX function is specifically designed to work with parent-child hierarchies to achieve this kind of calculation?
Model the data
A.PATHCONTAINS
B.PATHITEM
C.PATH
D.PATHLENGTH
Show answerAnswer
C. PATH
The PATH function is designed to return a delimited text string with the identifiers of all parents to the current row, starting from the oldest or top-most parent to the current. This function is foundational for working with parent-child hierarchies to enable calculations like summing values down the hierarchy.
18. A data modeler is optimizing a Power BI model with a complex star schema. The 'Sales' fact table has millions of rows and is related to several dimension tables including 'Products', 'Customers', and 'Date'. To improve query performance, the modeler wants to reduce the cardinality where possible. Which of the following columns, if present in the 'Sales' fact table, would be the most impactful to remove or move to a dimension table for performance optimization?
Model the data
A.SaleAmount (numeric value of the sale)
B.ProductCategory (text description, derived from ProductID)
C.DateKey (integer for the transaction date)
D.OrderID (unique identifier for each transaction)
Show answerAnswer
B. ProductCategory (text description, derived from ProductID)
ProductCategory, being a text column and a descriptive attribute derived from ProductID, has high cardinality and is best stored in the 'Products' dimension table. Keeping it in the fact table unnecessarily increases size and impacts performance.
19. A data modeler is designing a Power BI model for a retail chain. The model includes a 'Sales' table (Fact) and a 'Products' table (Dimension). The 'Products' table contains a 'CategoryID' column, and the 'Sales' table contains a 'ProductID' column. There is a one-to-many relationship between 'Products[ProductID]' and 'Sales[ProductID]'. The modeler needs to calculate the total sales for a specific category, regardless of any other filters applied to the 'Products' table, except for the category filter itself. Which DAX function should be used to achieve this?
Model the data
A.ALLEXCEPT
B.KEEPFILTERS
C.ALLSELECTED
D.ALL
Show answerAnswer
A. ALLEXCEPT
The ALLEXCEPT function removes all context filters from the specified table except for filters that have been applied to the specified columns. This allows for calculating totals while preserving specific column filters.
20. A data modeler is building a Power BI model for a global sales organization. The model contains a 'Sales' table with 'OrderID', 'OrderTotalLocalCurrency', and 'CurrencyID'. A 'Currency Exchange Rate' table contains 'CurrencyID', 'ExchangeRate', and 'Date'. The modeler needs to convert 'OrderTotalLocalCurrency' to USD based on the 'Date' of the order. Which DAX pattern enables this row-level currency conversion?
A. SUMX('Sales', 'Sales'[OrderTotalLocalCurrency] * RELATED('Currency Exchange Rate'[ExchangeRate]))
SUMX iterates through each row of the 'Sales' table, and for each row, RELATED retrieves the corresponding exchange rate from the 'Currency Exchange Rate' table based on the relationship, allowing for accurate row-level conversion before summing.
21. A data modeler is optimizing a Power BI model with a large 'Sales' fact table and several dimension tables. The model includes a 'Geography' dimension table with 'Country', 'State', and 'City' columns. The modeler observes that queries involving 'City' are significantly slower than those involving 'Country' or 'State'. The 'City' column has a very high number of unique values. Which modeling technique is most likely to improve query performance related to the 'City' column without losing analytical capability?
Model the data
A.Create a hierarchy in the 'Geography' table: 'Country' -> 'State' -> 'City'.
B.Mark the 'City' column as 'Hide in report view' to prevent direct use.
C.Split the 'Geography' table into separate 'Country', 'State', and 'City' tables, linked by IDs.
D.Implement a bi-directional cross-filter on the relationship between 'Sales' and 'Geography' tables.
Show answerAnswer
A. Create a hierarchy in the 'Geography' table: 'Country' -> 'State' -> 'City'.
Creating a hierarchy allows Power BI to optimize how data is processed and presented. When users interact with the hierarchy, Power BI can aggregate data at higher levels (Country, State) first, then drill down to 'City' as needed, reducing the initial load on the highly cardinal 'City' column. This improves performance without losing the ability to analyze by city.
22. A data analyst is performing data profiling in Power Query on a customer dataset. The 'Email' column is critical for uniqueness, but upon using 'Column profile' and 'Column quality' features, it's observed that there are several blank entries and some entries marked as 'Error'. The analyst needs to understand the exact count of these problematic entries to assess data quality. Which Power Query profiling metric provides this information directly?
Prepare the data
A.Column Profile
B.Column Quality
C.Column Statistics
D.Value Distribution
Show answerAnswer
B. Column Quality
The 'Column Quality' feature in Power Query Editor provides a direct, high-level overview of the column's health, showing percentages and counts for 'Valid', 'Error', and 'Empty' values. This directly addresses the need to understand the exact count of blank and error entries.
23. A data modeler is integrating data from multiple regional databases into a single Power BI model. The regional databases have slightly different column names for the same customer identifier (e.g., 'CustID', 'CustomerID', 'Client_ID'). To ensure a consistent and unified customer dimension table, what is the most effective ETL step the modeler should perform?
Model the data
A.Rename and standardize the customer ID columns in Power Query Editor.
B.Use the MERGE function in Power Query to combine the customer tables.
C.Create a new calculated column in Power BI Desktop to standardize the ID.
D.Establish cross-table relationships in Power BI Desktop using multiple columns.
Show answerAnswer
A. Rename and standardize the customer ID columns in Power Query Editor.
Renaming and standardizing columns in Power Query Editor is a crucial ETL step for data unification, ensuring consistent naming conventions before loading data into the model, which prevents issues with relationships and calculations.
24. A data modeler is creating a Power BI report that analyzes customer activity over time. The 'Customers' table contains 'CustomerID' and 'SignupDate'. The 'Activity' table contains 'ActivityID', 'CustomerID', and 'ActivityDate'. There is a one-to-many relationship from 'Customers[CustomerID]' to 'Activity[CustomerID]'. The modeler needs to calculate the number of active customers at the end of each month. An 'Active Customer' is defined as any customer who has had at least one activity within the last 90 days from the end of the current month. Which DAX expression should be used?
D. VAR EndOfMonth = LASTDATE('Date'[Date]) VAR DateRange = DATESBETWEEN('Date'[Date], EndOfMonth - 90, EndOfMonth) RETURN CALCULATE(DISTINCTCOUNT(Activity[CustomerID]), DateRange)
This measure requires defining a rolling 90-day window relative to the end of each month (which is determined by the filter context from the 'Date' table). Option C correctly captures this. 'LASTDATE('Date'[Date])' determines the end of the current month in the filter context. 'DATESBETWEEN('Date'[Date], EndOfMonth - 90, EndOfMonth)' then generates a table of all dates within that 90-day window. When this date table is passed as a filter to CALCULATE, it filters the 'Activity' table through the 'Date' table's relationship, ensuring that only activities within the specified 90-day period are considered for the distinct count of 'CustomerID'.
25. A data analyst is working with a large transactional dataset in Power Query. The 'TransactionAmount' column is currently of type Text and contains values like '€1,234.56', '$789.00', and '23.45'. The analyst needs to convert this column to a Decimal Number type for calculations. The challenge is that the currency symbols and comma thousands separators vary and must be removed, and the decimal separator is always a period. Which Power Query transformation, considering locale settings, is the most robust way to achieve this conversion?
Prepare the data
A.Change Type to Decimal Number using 'Replace Errors' after initial conversion.
B.Replace Value for currency symbols and commas, then Change Type to Decimal Number.
C.Change Type to Decimal Number using 'en-US' locale.
D.Change Type to Decimal Number (default locale)
Show answerAnswer
C. Change Type to Decimal Number using 'en-US' locale.
Changing type with locale settings is designed precisely for this scenario. By specifying a locale like 'en-US', Power Query correctly interprets the period as the decimal separator and ignores common currency symbols and comma thousands separators, making it the most robust and efficient method without requiring multiple 'Replace Value' steps.
Split Column by Number of Characters (Power Query)
Flip card
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
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
Data unification in ETL involves combining disparate data sources into a single, consistent, and coherent dataset, often requiring standardization of formats, naming, and structures.
Ensures consistency across multiple data sources.
Typically performed in the Power Query Editor (Extract, Transform, Load).
Includes renaming columns, changing data types, and handling missing values.
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.
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).
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.
Questions are original practice items written to match the published exam objectives. Step2Study is not affiliated with or endorsed by any certification body.