Operations & Systems

Decimal and Thousands Separators in Business Imports: Parse the Value Before Formatting It

The string 1,250 can describe one thousand two hundred fifty units or one and one-quarter units.

The characters have not changed. The interpretation has.

Decide which convention governs a quantity before assigning a numeric type. Preserve the original text, apply an explicit culture, and compare the parsed value with an independent expectation.

This article concerns correctly decoded numeric text in Power Query M. It does not repair character encoding, choose CSV delimiters, or convert currencies. The worksheet is a proposed review method. Every quantity, source declaration, and reconciliation below is synthetic.

Identify What Each Separator Does

Start with the field's purpose. A quantity supports arithmetic. An identifier names a record. Similar characters do not make those purposes interchangeable.

For this review, distinguish four layers:

Layer Role Question to answer
Field delimiter Separates fields in the delivered file Did the reader reconstruct the intended fields?
Decimal separator Separates the whole and fractional parts of a numeric representation Which character carries fractional meaning?
Grouping separator Groups digits in a numeric representation Which character, if any, groups digits?
Display format Presents a stored number to a reader How should the interpreted value appear?

These distinctions define the worksheet's vocabulary. Confirm each relevant convention with the source owner. Do not use a visual display preference as a parsing rule.

Keep a product code such as 001250 as text. Converting it to a number answers a different question and removes its original representation. Use the separate identifier-preservation procedure for those fields.

If a label contains damaged characters, resolve the UTF-8 import boundary first. Culture-aware numeric interpretation cannot establish which characters an incorrectly decoded file originally contained.

Obtain a Declaration for the Numeric Field

Ask for a field-specific rule. Record the unit, permitted sign, fractional precision, decimal separator, grouping convention, and applicable culture. Include examples supplied or accepted by the owner.

For a repeat delivery, put those choices in the CSV data contract. An explicit declaration gives the receiver something to enforce before a report depends on the values.

Do not infer the rule from the sender's office address. Do not borrow it from another column. One field may contain an identifier while a neighboring field contains a measured quantity.

Use magnitude as a second check. If the fictional field records material quantity in kilograms, ask what source evidence supports the expected amount. A familiar number of decimal places does not establish the unit or scale.

When the owner cannot confirm the convention, leave it unresolved. Record the question with representative original strings. A decision to hold a batch should tell the sender what information would release it.

Make Culture Explicit in Number.FromText

Microsoft documents Number.FromText with an optional culture argument that controls text interpretation. Its examples of accepted representations include ordinary decimal notation and scientific notation.

For a synthetic English United States declaration, write the culture into the expression:

Number.FromText("1,250.50", "en-US")

The expected value is 1250.5. For the equivalent synthetic German Germany declaration, use:

Number.FromText("1.250,50", "de-DE")

The expected value is also 1250.5. In this article's calculation notation, a period marks the decimal position and digits have no grouping punctuation. That notation makes the expected magnitudes easy to compare.

The culture argument selects an interpretation convention. It does not prove that the source supplied the intended quantity. These are documented-interface examples checked on October twenty-eighth, 2026. Verify them in your Power Query host before accepting a real import.

Microsoft describes culture as the locale convention used for numeric and date text. It documents document-level defaults and configurable regional settings. Record the query's configuration instead of assuming every application or workbook uses your computer's current preference. Microsoft: how culture affects text formatting.

Set the Locale Before Reviewing the Display

For a column conversion, Power Query provides Change type, then Using locale. Select the required numeric type and the locale that matches the source declaration. Microsoft: data type conversion using locale.

Record the host, installed version, selected type, selected locale, and relevant query step. This identifies the procedure someone else must repeat. The UI path here follows Microsoft's Power Query guidance; labels can depend on the host.

Inspect earlier type-setting steps too. Start the comparison from intact source text. If conversion already changed the value, adding another display format afterward does not recover the original representation.

Keep parsing and formatting as separate review decisions. First confirm the numeric value. Then choose how the report displays it. For the synthetic value 1250.5, a grouped display and an ungrouped display should represent the same quantity.

Do not accept a value because its final display resembles the source string. The worksheet needs an independent expected magnitude. A well-formatted mistake can pass a visual glance.

Copy a Numeric-Import Worksheet

Put the delivery version, field name, unit, owner, and rule version above the worksheet. Record one row per reviewed source record. Keep the record key available even when the public example omits it.

Field purpose Original text Culture evidence Parse rule Expected value Parsed value Reconciliation
[Quantity and unit] [Exact source characters] [Specification or owner declaration] [Function and explicit culture] [Independent numeric expectation] [Observed result or error] [Accept or hold, with reason]
[Identifier] [Exact code] [Text requirement] [Retain as text] [Exact code] [Observed text] [Character comparison]

Expected value should come from a source record or an agreed synthetic answer. Do not populate it from the same conversion you are checking.

Include the unit in the field purpose. A comparison between kilograms and units can produce matching digits without matching quantities. Keep unit conversion outside this parsing worksheet unless a separately documented step defines it.

Record “not attempted” when the convention is unresolved. Preserve an error when the chosen rule rejects a value. Avoid replacing either state with zero, which would add a quantity the source never established.

Use reconciliation to explain acceptance. Name the independent comparison and any remaining exception. The table should show the next action without requiring someone to read an entire email chain.

Reconcile a Synthetic Quantity Delivery

Consider four fictional records for material quantity in kilograms. The first two belong to separately declared exports. The last two arrive without a reliable numeric convention.

The parsed values below are expected demonstration results. They do not describe a live import or a tested Power Query refresh.

Field purpose Original text Culture evidence Parse rule Expected value Parsed value under proposed rule Reconciliation
Quantity, kilograms 1,250.50 Fictional export A declares en-US Number.FromText with en-US 1250.5 1250.5 Accept after observed result agrees
Quantity, kilograms 1.250,50 Fictional export B declares de-DE Number.FromText with de-DE 1250.5 1250.5 Accept after observed result agrees
Quantity, kilograms 1,250 No declaration Unresolved 1250 or 1.25 Not attempted Hold for source convention and quantity evidence
Quantity, kilograms 1.250 No declaration Unresolved 1.25 or 1250 Not attempted Hold for source convention and quantity evidence

Under these declared conventions, the first two records contribute 2501 kilograms together. Independently, 1250 kilograms plus 1250 kilograms gives 2500. The two half-kilogram fractions add one more kilogram.

For the third record, the candidate magnitudes differ by a factor of one thousand. A comma grouping interpretation yields 1250. A comma decimal interpretation yields 1.25. Neither candidate becomes authoritative because it fits a target total.

The fourth record reverses the punctuation but preserves the uncertainty. A period can carry fractional meaning in one declared convention and grouping meaning in another. Ask what the source intended.

Keep the unresolved records outside the accepted quantity total. Count them separately. For this example, two records contribute 2501 kilograms and two remain held. That statement preserves the denominator of the review.

Quarantine Mixed Formats Without Deleting Punctuation

Do not remove commas and periods indiscriminately to make a numeric conversion succeed. In the synthetic string 1.250,50, removing both produces 125050. That digit sequence is one hundred times the intended value 1250.5.

The transformed text may look easier to parse while carrying a different amount. A conversion rule must preserve meaning, not merely produce digits.

When a column mixes conventions, ask the owner to identify reliable partitions. Separate export A from export B using source metadata. Apply each declared rule to its own partition and retain that partition identity.

Do not select culture by whichever parser avoids an error. An ambiguous string may fit more than one interpretation. The absence of an error cannot choose the source convention.

If the owner cannot identify a reliable boundary, hold the batch or request a corrected export. Preserve every original string. Document which declaration or corrected record would resolve each exception.

Make sign, grouping placement, and permitted precision explicit acceptance conditions too. Do not treat a parser's tolerance as your entire business contract. Review a record against the declared field requirements after conversion.

Carry Magnitude Checks Into the Next Delivery

Keep the acceptance sample independent from the report's desired outcome. Write its expected quantities before running the proposed rule. Include the declaration used for each source partition. A sample chosen only after a total looks wrong cannot explain the original acceptance decision.

Ask a reviewer to trace one accepted quantity back to the untouched text. The explanation should name the source partition, culture, unit, expected value, and observed result. Matching digits alone do not complete that explanation.

Then trace a held record. The reviewer should identify the missing declaration and the person responsible for resolving it. Avoid a generic “bad data” label when the text is intact and the interpretation is unknown.

Retain both traces with the delivery receipt. They give the next importer examples of what acceptance requires and what an unresolved record looks like. Keep actual business records in the team's controlled workspace rather than copying them into a public procedure.

Use a small acceptance sample before importing the full delivery. Include a fractional value, a grouped value, an ambiguous value, and an identifier. Add a negative quantity only when the field's contract permits it.

Compare each result with its independent expectation. Reconcile the source count into accepted, held, and explicitly excluded records. Retain accepted totals with their unit and included population.

For the fictional delivery, a valid acceptance note names the two declared source partitions, their parsing rules, the 2501-kilogram accepted total, and two held records. It does not claim the held records contain zero kilograms.

When the supplier changes its export convention, revise the import rule before accepting the new format. Keep the previous rule with previous receipts. Recheck the same synthetic examples against the revised configuration.

The punctuation belongs to the value's interpretation. Keep the evidence attached.

Sources

Topic map: Spreadsheets, CSV files, and data handoffs

Articles in this cluster, then the 3 clusters that share the most topics with it.

This cluster: Spreadsheets, CSV files, and data handoffs

Neighbor: Business reporting and statistics

Neighbor: Records, access, and continuity

Neighbor: APIs, webhooks, and automation