Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data engineer is integrating data from a legacy system into Power BI. The system exports sales data in a fixed-width text file where each field occupies a specific number of characters, without delimiters. For example, a row might look like '20240101CUST001 PRODXYZ1234.56'. The engineer needs to extract '20240101' (Date), 'CUST001' (CustomerID), 'PRODXYZ' (ProductID), and '1234.56' (Amount) into separate columns. Which Power Query transformation is most suitable for this task?
- ASplit Column by Delimiter
- BSplit Column by Number of Characters
- CExtract Text After Delimiter
- DParse JSON
Show answer & explanationAnswer & explanation
Correct answer: B. Split Column by Number of Characters
Fixed-width text files are characterized by fields occupying a specific number of characters, not by delimiters. 'Split Column by Number of Characters' is precisely designed for this scenario, allowing you to define break points based on character positions to extract each field into its own column. The other options are for delimited or structured data.
Why the other options are wrong
- A. This file has no delimiters, so splitting by delimiter won't work.
- C. Extract Text After Delimiter also relies on delimiters, which are absent here.
- D. This is a plain text file, not JSON data.
Split Column by Number of Characters (Power Query)
A Power Query transformation used to divide a text column into multiple new columns by specifying fixed character lengths for each segment, ideal for fixed-width data files.
- Used for fixed-width data formats
- Splits based on character count from start/end or at specific positions
- Creates new columns for each segment.
Memory trick: Fixed Width: Count Characters, Cut Precisely.