Power BI Developer Interview Preparation Guide:

44. What are the key features of Power Query?

Power Query provides data connectivity to 150+ sources, graphical transformation interface, data profiling and quality assessment, merge and append operations, custom column creation, and M code editing for advanced scenarios.

45. What is Query Folding in Power Query?

Query Folding translates Power Query transformations into native SQL queries executed by the source database instead of the client machine. This improves performance by leveraging database optimization and reducing data transfer.

46. What transformations support Query Folding?

Transformations supporting Query Folding include filtering rows, removing columns, renaming columns, changing data types, grouping operations, and basic joins. Complex custom functions, adding custom columns with M code, and certain merge operations may break Query Folding.

47. How can you check if Query Folding is working?

Right-click on the last applied step in Query Editor and check if “View Native Query” option is available. If available, Query Folding is working; if grayed out, previous steps broke the folding.

48. What are the most common data shaping techniques in Power Query?

Common techniques include removing unnecessary columns and rows, splitting and merging columns, changing data types, filtering rows, replacing values, pivoting and unpivoting data, and appending or merging queries.

49. What is the difference between Merge and Append queries?

Merge combines tables horizontally based on matching columns (similar to SQL JOIN), creating wider tables. Append stacks tables vertically with similar structures (similar to SQL UNION), creating taller tables.

50. What are the different join types in Power Query?

Power Query supports Left Outer join, Right Outer join, Full Outer join, Inner join, Left Anti join (rows only in left table), and Right Anti join (rows only in right table).

51. How do you handle errors and null values in Power Query?

Use “Remove Errors” to eliminate problematic rows, “Replace Errors” for alternative values, filter out nulls when invalid, replace nulls with defaults when appropriate, and use conditional logic for different scenarios.

52. What is data profiling in Power Query?

Data profiling analyzes data quality showing column distribution, column quality (valid, error, empty percentages), and column profile statistics. Enable it from View tab to assess data quality and identify issues.

53. What are query parameters in Power Query?

Query parameters are variables storing values that can be referenced throughout queries. They enable dynamic filtering, parameterized data sources, and reusable query logic for flexible reporting.

54. How do you create custom functions in Power Query?

Select a query, right-click and choose “Create Function,” or manually write M code defining parameters and logic. Functions enable code reusability and can be invoked across multiple queries.

55. What is the Advanced Editor in Power Query?

Advanced Editor displays and allows editing of M code for queries. Access it from Home or View tab to view step-by-step M code, debug syntax errors, and implement advanced transformations.

56. What are query dependencies in Power Query?

Query dependencies show relationships between queries where one query references another. View dependencies from the “View Query Dependencies” option to understand data flow and impact analysis.

57. What is the difference between Table.AddColumn and Table.ExpandRecordColumn?

Table.AddColumn creates new columns with custom logic or calculations. Table.ExpandRecordColumn expands record-type columns into separate columns, commonly used after merging queries.

58. How do you handle circular dependencies in Power Query?

Break circular dependencies by restructuring query logic, using intermediate queries, implementing parameters instead of query references, or redesigning data flow architecture.

59. What is List.Generate function in M language?

List.Generate creates lists dynamically based on initial value, condition, next value, and selector functions. It’s useful for creating date tables, sequences, or iterative calculations.

60. What are the performance best practices in Power Query?

Filter rows early in transformation steps, remove unnecessary columns before other operations, leverage Query Folding, avoid using Text.Combine on large datasets, use Table.Buffer sparingly, and limit complex custom functions.

61. What is Table.Buffer function and when should it be used?

Table.Buffer loads entire table into memory preventing re-evaluation. Use it when referencing the same table multiple times in subsequent steps, but avoid for large tables due to memory consumption.

62. How do you unpivot columns in Power Query?

Select columns to keep as identifiers, select columns to unpivot, then choose “Unpivot Columns” from Transform tab. This converts wide data format to long format suitable for analysis.

63. What is the purpose of Pivot Column transformation?

Pivot Column converts long format data to wide format by rotating unique values from one column into multiple columns. It’s useful for creating cross-tabular views and summary reports.

64. How do you create a date table in Power Query?

Use List.Dates to generate date range, convert to table, add calculated columns for Year, Quarter, Month, Week, Day, and mark as date table. Alternatively, use CALENDAR or CALENDARAUTO DAX functions.

65. What is the difference between Power Query in Excel and Power BI?

Power BI’s Power Query supports more data sources, has better performance for large datasets, integrates with dataflows, and provides cloud-based transformation capabilities. Excel’s Power Query outputs to worksheets or data models locally.

66. How do you reference other queries in Power Query?

Right-click a query and select “Reference” to create dependent query, or use query name directly in M code. Referenced queries automatically update when source changes.

67. What is the difference between Reference and Duplicate queries?

Reference creates dependent query pointing to original source sharing transformation steps. Duplicate creates independent copy with its own transformation logic not linked to original.

68. How do you work with JSON data in Power Query?

Connect to JSON source, Power Query recognizes structure, expand records and lists into columns, navigate nested hierarchies, and transform into tabular format.

69. What are query groups in Power Query?

Query groups organize queries into folders for better management in complex models with many queries. They improve readability but don’t affect functionality or performance.

70. How do you handle authentication in Power Query?

Power Query supports multiple authentication methods: Windows, Database, Microsoft Account, Organizational Account, API Key, OAuth, and Web API credentials. Configure through Data Source Settings.

71. What is data privacy level in Power Query?

Privacy levels (Private, Organizational, Public) prevent unintentional data combination from different security contexts. They determine how Power Query can combine data from multiple sources.

72. How do you split columns in Power Query?

Use “Split Column” with options: By Delimiter, By Number of Characters, By Positions, By Lowercase to Uppercase, or By Digit to Non-Digit. This separates data into multiple columns.

73. What is conditional column in Power Query?

Conditional columns create new columns based on if-then-else logic without writing M code. Access from Add Column tab providing intuitive interface for business rules.

74. How do you extract values from lists in Power Query?

Use Extract Values transformation to convert lists into delimited text strings. Alternatively, expand lists into rows or use List functions in M code.

75. What is Table.Group function in M language?

Table.Group aggregates data similar to SQL GROUP BY. It groups rows by key columns and calculates aggregations like Sum, Count, Average, Min, Max on grouped data.

76. How do you work with web data in Power Query?

Use “From Web” connector providing URL, handle authentication if required, navigate HTML tables or JSON/XML responses, and apply transformations. Anonymous, Windows, Web API, or Organizational authentication available.

77. What are query diagnostics in Power Query?

Query Diagnostics captures detailed performance information about query execution including durations, data source queries, and resource usage. Enable from Tools menu to troubleshoot performance issues.

78. How do you handle dynamic file paths in Power Query?

Create parameters for file paths, use folder connections with filtering, or programmatically construct paths using M functions. This enables flexible data source management.

79. What is Table.TransformColumnTypes function?

Table.TransformColumnTypes changes data types for multiple columns simultaneously in M code. It’s equivalent to manually changing types in Query Editor but more efficient for multiple columns.

80. How do you combine multiple files from a folder?

Use “From Folder” connector, filter files, select “Combine Files” which creates example transformation applied to all files. Power Query automatically generates helper queries for the pattern.

191. How do you implement Row-Level Security (RLS)?

RLS restricts data access for specific users at row level: create roles in Power BI Desktop using DAX filter expressions, test roles using “View as Role” feature, publish to Service, and assign users/groups to roles. Example: [Region] = USERNAME() restricts users to their region’s data.

192. What is the difference between Static and Dynamic RLS?

Static RLS has hardcoded filters for each role like [Region] = “East”. Dynamic RLS uses functions like USERPRINCIPALNAME() to filter based on logged-in user enabling automatic personalization without creating multiple roles. Dynamic RLS is more scalable and maintainable.

193. How do you implement Dynamic RLS?

Create user mapping table with columns like Username and Region/Territory, relate it to data tables, create role with DAX filter [Username] = USERPRINCIPALNAME(), test using “View as Role”, and publish with user assignments. Dynamic RLS automatically filters based on current user.

194. Can users belong to multiple RLS roles?

Yes, when users belong to multiple roles Power BI applies union of all filters meaning users see data permitted by any of their roles. This creates additive permissions rather than restrictive ensuring users have access to combined data sets.

195. How do you test RLS in Power BI Desktop?

Use “View as” option under Modeling tab to simulate view for specific roles or specific users. This validates filters work correctly before publishing preventing security issues in production.

196. What is the Power BI Gateway?

Power BI Gateway acts as bridge between Power BI Service and on-premises data sources enabling scheduled refresh and DirectQuery for local databases. Two modes exist: Personal Gateway (single user) and Enterprise Gateway (organizational, centrally managed).

197. What is the difference between Personal and Enterprise Gateway?

Personal Gateway supports one user’s datasets with limited features suitable for individual use. Enterprise Gateway supports multiple users and datasets with centralized management, high availability, better performance, and advanced features like RLS passthrough. Enterprise Gateway is recommended for organizations.

198. How do you schedule dataset refresh in Power BI Service?

Navigate to dataset settings, configure data source credentials, set refresh schedule (daily/weekly times), specify time zone, and optionally enable refresh failure notifications. Pro users get 8 daily refreshes, Premium users get 48.

199. What are workspace roles in Power BI Service?

Workspace roles include Admin (full control including deletion), Member (publish, delete, share content), Contributor (publish content, no workspace settings), and Viewer (view content only). Roles control access levels and capabilities within collaborative environments.

200. How do you share Power BI reports?

Share via direct sharing to individual users with email notification, publish to web (public access without authentication), embed in SharePoint or Teams, create and distribute apps, or share workspace access. Choose method based on audience and security requirements.

201. What are Power BI Apps and how do they differ from workspaces?

Apps are packaged collections of dashboards and reports for distribution to large audiences with simplified navigation and read-only access. Workspaces are collaborative environments for content creation. Apps provide controlled content delivery while workspaces enable development collaboration.

202. What is Power BI Mobile and its capabilities?

Power BI Mobile apps for iOS, Android, and Windows enable on-the-go report access with touch-optimized interfaces, offline viewing, barcode scanning, location-based data, annotations, alerts, and mobile layouts.

203. How do you create mobile-optimized reports?

Use Phone Layout view in Power BI Desktop to arrange visuals specifically for mobile screens creating portrait-oriented layouts. Design simplified versions with fewer visuals, larger touch targets, and mobile-appropriate formatting.

204. What are dataflows in Power BI?

Dataflows are reusable ETL processes in Power BI Service that extract, transform, and load data into Azure Data Lake Storage. They enable centralized data preparation, promote reusability across multiple datasets, and support Common Data Model schemas.

205. What is the difference between dataflows and datasets?

Dataflows focus on data preparation and transformation storing results in Azure Data Lake for reuse across multiple datasets. Datasets are complete data models with relationships and measures used directly in reports. Dataflows enable separation of ETL from reporting.

206. What are Power BI alerts?

Alerts send notifications when data thresholds are crossed on dashboard tiles for KPIs, gauges, and cards. Configure alert conditions, notification frequency, and delivery methods enabling proactive monitoring.

207. How do you implement subscription in Power BI?

Subscriptions email report snapshots on schedule to users or distribution lists. Configure from report menu setting frequency, filters, and recipients enabling automated report distribution.

208. What is Power BI Embedded?

Power BI Embedded enables embedding Power BI content in custom applications using capacity-based pricing rather than per-user licensing. It provides white-label capabilities for ISVs and developers integrating analytics into products.

209. What are deployment pipelines?

Deployment pipelines provide CI/CD capabilities with Development, Test, and Production stages. They enable version control, streamlined content promotion, parameter rules, and reduce manual deployment errors.

210. How do you monitor Power BI usage?

Use Admin Portal’s usage metrics, audit logs, and activity logs to track user access, report views, refresh history, and capacity utilization. Premium capacity metrics app provides detailed performance monitoring. Monitoring enables governance, optimization, and compliance tracking.

The STAR method structures behavioral responses into Situation (20% – setting context), Task (10% – defining responsibility), Action (60% – detailing personal contributions), and Result (10% – quantifying outcomes). For Power BI developers, emphasize technical decisions, stakeholder collaboration, and measurable business impact through data visualization and analytics solutions.