Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data engineer is designing a semantic model in Microsoft Fabric. The model will consume data from various sources, including a SQL Server database, a CSV file from a SharePoint folder, and a REST API. To ensure data consistency and enable advanced calculations, the engineer needs to apply several transformation steps to the data, such as merging tables, creating custom columns, and pivoting data, before it is loaded into the model. Which tool or feature within Microsoft Fabric is primarily used for these data preparation and transformation tasks?
- ASQL queries directly in the semantic model
- BPower Query Editor
- CVisual Studio Code with Tabular Editor
- DDAX in the semantic model
Show answer & explanationAnswer & explanation
Correct answer: B. Power Query Editor
The Power Query Editor (Get Data / Transform Data experience in Fabric) is the primary tool for data preparation and transformation within Microsoft Fabric. It allows connecting to various data sources, performing complex transformations like merging, custom column creation, and pivoting, and then loading the cleaned data into the semantic model.
Why the other options are wrong
- A. SQL queries are used to extract data from SQL databases but are not a universal tool for transforming data from all listed sources (CSV, REST API) or for complex operations like pivoting within the semantic model itself.
- C. Visual Studio Code with Tabular Editor is used for advanced semantic model definition, scripting, and deployment (e.g., OLS, advanced properties) but not for the initial data ingestion and transformation phase.
- D. DAX is used for creating measures, calculated columns, and calculated tables within the semantic model, not for initial data transformation from diverse sources.
Power Query Editor (Fabric)
The Power Query Editor in Microsoft Fabric is a visual tool used for connecting to diverse data sources, performing data cleansing, transformation, and shaping operations before loading data into a semantic model.
- Supports hundreds of data connectors.
- Uses the M language for transformations.
- Essential for data preparation in semantic models.
Memory trick: Connect, transform, load – Power Query's the data's road.