Microsoft Power BI Data Analyst

You need to connect to a SQL Server database that requires Windows Authentication from Power BI Desktop. What is the recommended approach?

• AUse the SQL Server connector with Database authentication and enter Windows credentials
• BUse the SQL Server connector and select "Windows" authentication method
• CInstall the SQL Server ODBC driver and use the ODBC connector
• DExport the SQL Server data to Excel first, then import from Excel

In Power Query, you have a column containing mixed date formats (e.g., "01/15/2024", "2024-01-15", "15-Jan-2024"). Which transformation should you use to standardize all values to a consistent date format?

• ASplit Column by Delimiter, then reassemble the parts
• BReplace Values to rewrite each format variant
• CChange Type with Locale
• DA custom column built with Text.Combine

What is the primary difference between Import mode and DirectQuery mode when connecting to a SQL Server database?

• AImport mode is faster because it applies weaker security than DirectQuery
• BImport caches data in the model; DirectQuery queries the source live
• CDirectQuery works with a wider range of data sources than Import
• DImport mode requires a Power BI Premium license while DirectQuery does not

You need to combine data from two Excel files with identical structures located in the same SharePoint folder. Which Power Query approach is most efficient?

• AImport each file separately and create a relationship between them
• BUse the SharePoint folder connector to combine the files
• CCopy and paste data from both files into a single Excel file first
• DUse Append Queries to combine the tables after importing each file

A Store dimension is identified only by Region plus StoreCode together, so no single column is unique. How do you make it relatable to the fact table?

• AMerge both columns into one key column
• BCreate a relationship on both columns at once
• CSet the relationship cardinality to many-to-many
• DSort both tables by Region and StoreCode

You need to create a reusable parameter for a file path that will be used across multiple queries. Which Power Query feature should you use?

• ACreate a custom function
• BCreate a Query Parameter
• CUse a global variable in Advanced Editor
• DHardcode the path in each query

What happens when you select "Remove Duplicates" on a column in Power Query?

• AIt deletes duplicate values from the data source permanently
• BIt removes rows where all column values are identical to another row
• CIt keeps only the first row per distinct value in that column
• DIt creates a separate table with only the duplicate rows

You have a column with full names in the format "LastName, FirstName" and need to split it into two separate columns. Which M function would achieve this?

• AText.BeforeDelimiter and Text.AfterDelimiter
• BText.Start and Text.End with a fixed character count
• CText.Split followed by Text.Combine
• DText.Replace paired with Text.Insert

What is the purpose of the Privacy Levels feature in Power Query?

• ATo restrict who can open the published report in the Power BI service
• BTo control whether sources may be combined
• CTo encrypt data in transit between the source and Power BI
• DTo set row-level security rules for report consumers

You need to unpivot a table that has months as columns (Jan, Feb, Mar, etc.) and create a single "Month" column with the values in a "Sales" column. Which transformation should you use?

• ATranspose the table
• BPivot Column using Month
• CUnpivot Columns
• DReverse Rows and rename

Which Power Query transformation would you use to group rows by a specific column and calculate aggregations like Sum, Average, or Count?

• AMerge Queries
• BAppend Queries
• CGroup By
• DSort rows by that column

You need to conditionally replace values in a column based on another column's values. For example, if Region is "International", set Currency to "USD". Which approach is best?

• AUse the Replace Values transformation
• BAdd a Custom Column with an if expression
• CUse the Replace Errors transformation
• DManually edit each affected cell in the preview grid

What happens to your Power Query transformations if the underlying data source structure changes (e.g., a column is renamed or removed)?

• AThe query automatically adapts to the new structure
• BThe query errors and the affected steps must be fixed by hand
• CPower BI automatically creates a backup of the original query
• DThe query continues to work but ignores the changed columns

In a star schema design, what is the primary purpose of a dimension table?

• ATo store numeric measures and metrics for analysis
• BTo supply the attributes used for filtering and grouping
• CTo establish many-to-many relationships between fact tables
• DTo optimize query performance through data compression