Practical Power BI Interview Questions

let Source = Sql.Database("server", "SalesDB"), Orders = Source{[Schema="dbo", Item="Orders"]}[Data], Filtered = Table.SelectRows(Orders, each [OrderDate] >= #date(2024, 1, 1)), Kept = Table.SelectColumns(Filtered, {"OrderID", "OrderDate", "CustomerID", "Amount"}), Typed = Table.TransformColumnTypes(Kept, {{"Amount", Currency.Type}, {"OrderDate", type date}}) in Typed All these steps fold to SQL on a database source.

let StartDate = #date(2022, 1, 1), EndDate = #date(2026, 12, 31), Days = Duration.Days(EndDate - StartDate) + 1, Dates = List.Dates(StartDate, Days, #duration(1, 0, 0, 0)), T = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}), Typed = Table.TransformColumnTypes(T, {{"Date", type date}}), Y = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type), M = Table.AddColumn(Y, "Month", each Date.MonthName([Date]), type text) in M

Use Add Column, Conditional Column and define rules, or write M directly: = Table.AddColumn(Source, "Band", each if [Amount] >= 100000 then "High" else if [Amount] >= 25000 then "Medium" else "Low", type text) Adding the type at the end avoids an extra Changed Type step. Conditional columns built here are computed at refresh, not at query time.

Select the column, use Transform, Split Column by Delimiter (space, leftmost) to get first and last names. Then use Format, Trim and Clean to remove extra spaces and non-printable characters, and Capitalize Each Word for consistent case. In M: = Table.TransformColumns(Source, {{"Name", each Text.Proper(Text.Trim(_)), type text}})

Write a query for one file, turn the file path into a parameter, then right-click and Create Function. Or write it in M: (path as text) as table => let Src = Excel.Workbook(File.Contents(path), true), Sheet = Src{[Item="Sales", Kind="Sheet"]}[Data] in Sheet Then invoke it on a column of file paths with Add Column, Invoke Custom Function and expand the results.

Date = ADDCOLUMNS( CALENDAR(DATE(2022, 1, 1), DATE(2026, 12, 31)), "Year", YEAR([Date]), "Month No", MONTH([Date]), "Month", FORMAT([Date], "MMM"), "Quarter", "Q" & QUARTER([Date]), "Weekday", FORMAT([Date], "ddd") ) Then mark it as a date table and sort Month by Month No.

Fiscal Year = VAR m = MONTH('Date'[Date]) VAR y = YEAR('Date'[Date]) RETURN "FY " & IF(m >= 4, y, y - 1) & "-" & RIGHT(IF(m >= 4, y + 1, y), 2) For April 2026 this returns FY 2026-27, which matches the Indian financial year. Add a fiscal month number column for sorting too.

Total Sales = SUM(Sales[Amount]) Total Cost = SUMX(Sales, Sales[Qty] * Sales[UnitCost]) Profit = [Total Sales] - [Total Cost] Margin % = DIVIDE([Profit], [Total Sales]) Format Margin % as a percentage. Building measures on other measures keeps logic in one place and easy to change.

Sales % of Total = DIVIDE( [Total Sales], CALCULATE([Total Sales], REMOVEFILTERS('Product')) ) This gives each product's share of total across all products. Use ALLSELECTED('Product') instead if the denominator should respect slicer selections on products.

Electronics North Sales = CALCULATE( [Total Sales], 'Product'[Category] = "Electronics", 'Store'[Region] = "North" ) Multiple filter arguments are combined with AND. Wrap a filter with KEEPFILTERS if it should intersect with an existing slicer selection instead of replacing it.

Customer Band = VAR s = CALCULATE(SUM(Sales[Amount])) RETURN SWITCH(TRUE(), s >= 500000, "Platinum", s >= 100000, "Gold", s > 0, "Silver", "No Purchase") This is a column on the Customer table; CALCULATE triggers context transition so each row gets its own customer's sales.

Big Customers = COUNTROWS( FILTER( VALUES(Customer[CustomerID]), [Total Sales] > 10000 ) ) Iterating VALUES of the customer key keeps the loop small and respects current filters such as year or region.

Sales YTD = TOTALYTD([Total Sales], 'Date'[Date], "31/3") Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) YoY % = VAR cur = [Total Sales] VAR py = [Sales PY] RETURN DIVIDE(cur - py, py) The "31/3" argument makes YTD follow an April to March fiscal year.

Sales 3M Avg = VAR periods = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH) RETURN CALCULATE( AVERAGEX(VALUES('Date'[Month Year]), [Total Sales]), periods ) AVERAGEX over months averages monthly totals instead of daily values. Month Year must be a column in the date table.

Running Total = VAR maxDate = MAX('Date'[Date]) RETURN CALCULATE( [Total Sales], 'Date'[Date] <= maxDate, REMOVEFILTERS('Date') ) REMOVEFILTERS clears the current period filter so all earlier dates are included. In a visual-only scenario, the visual calculation RUNNINGSUM is simpler.

Product Rank = IF( ISINSCOPE('Product'[Product]), RANKX(ALLSELECTED('Product'[Product]), [Total Sales], , DESC, DENSE) ) ALLSELECTED ranks within the products visible after slicers, and ISINSCOPE hides the rank on total rows.

Report Title = VAR r = SELECTEDVALUE('Store'[Region], "All Regions") VAR y = SELECTEDVALUE('Date'[Fiscal Year], "All Years") RETURN "Sales for " & r & " - " & y Use it in a visual title through conditional formatting, Title text, Field value. SELECTEDVALUE returns the fallback text when zero or multiple values are selected.

New Customers = VAR minDate = MIN('Date'[Date]) RETURN COUNTROWS( FILTER( VALUES(Sales[CustomerID]), CALCULATE(MIN(Sales[OrderDate]), REMOVEFILTERS('Date')) >= minDate ) ) A customer is new if their first ever order falls in the current period.

Sales Colour = VAR ach = DIVIDE([Total Sales], [Target]) RETURN SWITCH(TRUE(), ach < 0.9, "#D64550", ach < 1, "#F2A93B", "#2E9E5B") In the visual, open conditional formatting for background or font colour, choose Format style Field value and select this measure.

// Security table: Email, Region; related to Region[Region] // Role filter on Security table: [Email] = USERPRINCIPALNAME() // Optional all-access rule on Region table instead: VAR u = USERPRINCIPALNAME() RETURN LOOKUPVALUE(Security[Region], Security[Email], u) = "ALL" || Region[Region] IN CALCULATETABLE(VALUES(Security[Region]), Security[Email] = u)

Before: Growth = IF([Sales PY] = 0, BLANK(), ([Total Sales] - [Sales PY]) / [Sales PY]) After: Growth = VAR cur = [Total Sales] VAR py = [Sales PY] RETURN DIVIDE(cur - py, py) The previous year measure is now evaluated once instead of three times, and DIVIDE handles zero safely.

EVALUATE SUMMARIZECOLUMNS( 'Date'[Fiscal Year], 'Product'[Category], "Sales", [Total Sales], "Margin", [Margin %] ) ORDER BY 'Date'[Fiscal Year] Turn on Server Timings and Clear cache before running to see storage engine and formula engine time. The same EVALUATE syntax works in the DAX query view in Desktop.

= Table.AddColumn(Source, "OrderDateOnly", each DateTime.Date([OrderDateTime]), type date) = Table.AddColumn(Prev, "OrderHour", each Time.Hour([OrderDateTime]), Int64.Type) Then remove the original OrderDateTime column. A datetime with seconds can have millions of unique values, while date and hour columns have only thousands and 24 values, compressing much better.

Use Keep Errors on a copy of the query to inspect the bad rows, then fix the cause, such as wrong data types or invalid dates. Replace Errors can substitute a value, and in M try ... otherwise catches errors per value: = Table.AddColumn(Source, "AmountNum", each try Number.From([Amount]) otherwise null, type nullable number) Log error rows to a separate query so they can be reviewed rather than silently dropped.

Closing Balance = CALCULATE( SUM(Inventory[Qty]), LASTNONBLANK('Date'[Date], CALCULATE(COUNTROWS(Inventory))) ) Balances are semi-additive: they sum across warehouses but not across time. This takes the last date with data in each period. LASTDATE works when balances exist for every day.