This is a frustrating problem, but it's also a deeply familiar one. The root cause here is not a bug in Power Query, it's a collision between how your CSV file stores numbers and how Excel's import logic interprets regional settings. When your database writes `155.746,88` as a string, Power Query sees the dot as a thousands separator and the comma as a decimal symbol, exactly as your European locale would expect. The trouble begins when the import engine decides to strip out all separators before applying its own decimal logic, effectively treating the entire number as an integer and then appending `,00` as a default decimal. That's why `155.746,88` becomes `15.574.688,00`: the dot and comma are removed, the remaining digits `15574688` are parsed as a whole number, and then Power Query forces two decimal places, shifting the magnitude by a factor of 100.
The practical consequence is that you cannot rely on automatic data type detection, not because the data is corrupted, but because Power Query is making a best guess based on the first few rows, which may not reflect the full pattern of your dataset. When it guesses "Text" for the column, it is actually safer than guessing "Decimal" incorrectly. The error you see when you manually switch to a numeric type is Power Query admitting it cannot reconcile the string format with its internal number model. Fixing this requires explicit, manual steps in the Power Query editor. You need to tell Power Query to treat the entire column as text during import, then use the "Replace Values" transformation to swap the dot with nothing and the comma with a dot (or the reverse, depending on your target locale), and finally change the column type to decimal after that cleanup. This two-step process, strip separators, then parse, gives you control over the order of operations.
What this says about your organization's data workflow is worth noting. CSV files are a universal interchange format, but they carry no metadata about locale or number formatting. When a database exports numbers as localized strings, it outsources the interpretation problem to every downstream consumer. The real fix is not a Power Query workaround; it is a database export setting that writes numbers in a neutral format, either using a dot as the decimal separator with no thousands separator, or using a consistent ISO standard like `155746.88`. Until that change is made, every department that imports these CSVs will need to replicate your manual transformation steps. That is not a technical failure, it is a process failure dressed up as a data problem.
So here is the concrete takeaway: stop fighting Power Query's automatic guesses. Set the import step to "Text" for every numeric column in the affected CSVs, apply the character replacement logic before any type conversion, and then convert. Test it on a single file, document the exact sequence of steps, and share that with your colleagues. Once the process is repeatable, push upstream for the database team to adopt a locale-agnostic export format. That is the only way to ensure the next CSV import does not turn `155.746,88` into a four-hour debugging session.