30.2 Data Connectivity and Power Query

Q7. Explain Import, DirectQuery and Live connection.

Import copies data into the in-memory model – fastest, full features, needs refresh. DirectQuery leaves data in the source and queries it live – fresh data, large volumes, but slower and with some limits. Live connection connects to an existing published semantic model or Analysis Services model – the report has no model of its own.

Q8. What is a composite model?

A model that combines storage modes – for example Import tables and DirectQuery tables, or a local model extended on top of a published semantic model. Tables can be Import, DirectQuery or Dual.

Q9. What is Power Query and what language does it use?

Power Query is the ETL/data preparation engine in Power BI. Each transformation is recorded as a step in the M language (Power Query formula language), which is case-sensitive.

Q10. What is the difference between Append and Merge?

Append stacks tables vertically (adds rows, like SQL UNION). Merge joins tables horizontally using a key column (adds columns, like SQL JOIN).

Q11. What join kinds are available in Merge Queries?

Left outer, Right outer, Full outer, Inner, Left anti and Right anti. Anti joins return rows with no match – useful for data quality checks.

Q12. What is query folding and why is it important?

Query folding is when Power Query converts transformation steps into a native source query (such as SQL) so the source does the processing. It improves refresh performance, is essential for DirectQuery, and is needed for incremental refresh to be effective. Check it using View Native Query or the step folding indicators.

Q13. What is the difference between Unpivot Columns and Unpivot Other Columns?

Unpivot Columns converts the selected columns into attribute–value rows. Unpivot Other Columns converts all columns except the selected ones, so new columns added later in the source are also unpivoted automatically – usually the safer option.

Q14. Reference vs Duplicate query?

Duplicate copies all steps into an independent query. Reference creates a new query that starts from the output of the original, so changes to the original flow through.

Q15. How do you handle data quality issues in Power Query?

Use the data profiling tools (Column quality, distribution, profile), set correct data types, Trim/Clean text, Replace values and errors, Remove duplicates/blank rows, Fill down, and anti-joins to find unmatched keys.

Q16. What are parameters used for in Power Query?

To make queries dynamic – for example switching server names, file paths or filter values between development and production – and for incremental refresh (RangeStart/RangeEnd).