Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is working in Power Query with a table containing customer data. The table includes a column named `CustomerName` and a column named `CustomerID`. The analyst needs to create a new column named `CustomerIdentifier` that combines these two columns into a single string, formatted as 'Name (ID)'. For example, if `CustomerName` is 'John Doe' and `CustomerID` is 'C123', the new column should show 'John Doe (C123)'. Which Power Query transformation should the analyst use?
- AConditional Column
- BMerge Queries
- CAdd Custom Column
- DGroup By
Show answer & explanationAnswer & explanation
Correct answer: C. Add Custom Column
To combine existing columns with custom formatting, 'Add Custom Column' is the appropriate transformation. It allows you to write an M formula that concatenates the `CustomerName` and `CustomerID` columns with literal text. Merge Queries joins tables, Group By aggregates, and Conditional Column uses if/then logic.
Why the other options are wrong
- A. Conditional Column creates a new column based on if/then/else logic, not for direct concatenation of multiple columns.
- B. Merge Queries combines two separate tables, not columns within the same table.
- D. Group By aggregates rows based on common values, not for concatenating columns.
Add Custom Column (Power Query)
A Power Query transformation that creates a new column by applying a custom M formula to existing columns or values, enabling complex calculations, concatenations, or derivations.
- Uses M language for custom logic
- Creates new, derived columns
- Versatile for various data manipulation tasks
Memory trick: Custom Columns: Craft New Data with Code.