How to Convert Text with Commas to Number in Power Query
You are preparing data for a Power BI report. The source data contains a column with values like '1,234.56' formatted as text. You need to convert this to a numeric value for calculations. What is the best approach?
Quick Answer
The answer is to replace the comma with an empty string in Power Query Editor, then change the data type to Decimal Number. This approach is correct because it cleans the text-based numeric data directly in the M language transformation layer, which is far more efficient and scalable than using DAX calculated columns in the data model. When you convert text with commas to number in Power Query, you remove the thousands separator that prevents automatic type detection, allowing Power Query to interpret the value as a true decimal. On the PL-300 exam, this scenario tests your understanding of data preparation best practices, often appearing as a distractor where candidates might incorrectly choose to handle the conversion via DAX or by changing the locale settings. A common trap is assuming Power BI will automatically parse regional number formats, but Power Query requires explicit steps. Memory tip: think “strip the comma, then decimal” — always clean text before changing the data type.
⚠ Common exam trap
Microsoft often tests the misconception that the 'Clean' transform removes all non-numeric characters, but in reality it only removes non-printable control characters, not punctuation like commas or periods.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
In Power Query Editor, replace the comma with an empty string, then change the data type to Decimal Number.
Power Query Editor provides the most efficient and scalable method for cleaning and converting text-based numeric data. By replacing the comma with an empty string and then changing the column data type to Decimal Number, you perform the transformation directly in the data preparation layer (M language), which is optimized for performance and avoids the overhead of DAX calculated columns. This approach also ensures the data remains clean for all downstream calculations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
In Power Query Editor, split the column by comma and then use the second part.
Why it's wrong here
Splitting loses the integer part.
- ✗
In DAX, create a calculated column using VALUE() after removing commas.
Why it's wrong here
Better to clean data in Power Query before loading.
- ✓
In Power Query Editor, replace the comma with an empty string, then change the data type to Decimal Number.
Why this is correct
This removes the formatting and converts to number.
- ✗
In Power Query Editor, use the 'Clean' transform to remove non-numeric characters.
Why it's wrong here
Clean removes non-printable characters, not commas.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are transforming data in Power Query. A column named 'SalesAmount' contains values as text with a dollar sign and thousands separator, e.g., "$1,234.56". You need to convert this column to a decimal number for analysis. What is the most efficient sequence of transformations?
easy- A.Split the column by delimiter and keep the numeric part, then change data type.
- B.Change data type to Decimal Number directly; Power Query will automatically clean the values.
- ✓ C.Use Replace Values to remove '$' and ',', then change data type to Decimal Number.
- D.Use Replace Values to remove '$' and ',' then change data type to Decimal.
Why C: It explicitly removes both the dollar sign and the comma using Replace Values before changing the data type to Decimal Number, ensuring proper conversion without errors. Option A is inefficient; splitting the column is unnecessary when simple replacements work. Option B would fail because Power Query cannot automatically parse currency symbols and thousands separators from text when changing data type directly. Option D appears similar but specifies 'Decimal' instead of 'Decimal Number', which is not a valid data type in Power Query, leading to an error or incorrect result.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.