Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
iulia2006 la Simpatie.ro
 | Femeie
24 ani
Bucuresti
cauta Barbat
25 - 49 ani |
|
|
TRaP
Moderator
Inregistrat: acum 8 ani
Postari: 916
|
|
I have a table where the source of the data added various notations to the km column: "KM", " KM", " KM ", " km". But not for all values, some were numbers without any notation.
As far as I know, these cannot be all removed in 1 step, except it's a formula like the one below.
Prior to this step, I converted the column format to Text to prevent errors on the values that were already numbers.
= Table.TransformColumns(
#"Changed Type - distance to text",
{
{
"Distance to destination (km)",
each
let
chars = Text.ToList(Text.From(_)),
filtered = List.Select(chars, each _ >= "0" and _ <= "9" or _ = "." or _ = ","),
result = Text.Combine(filtered, "")
in
try Number.FromText(result) otherwise "ERROR, VERIFY DATA",
type number
}
}
) |
Source: ChatGPT
|
|
| pus acum 6 zile |
|
|
TRaP
Moderator
Inregistrat: acum 8 ani
Postari: 916
|
|
New development for the above, it looks like there were 2 values that were actually in meters not km.
The formula gets a bit longer now, and a bit more complicated.
Added comments in this color.
= Table.TransformColumns(
#"Changed Type - distance to text",
{
{
"Distance to destination (km)",
each
let
txt = Text.Trim(Text.Lower(Text.From(_))), // make text case non-dependent
isMeters = Text.Contains(txt, "m") and not Text.Contains(txt, "km"), // find out if text is meters and not km
chars = Text.ToList(txt), // make a list of the characters in the cell
filtered = List.Select(
chars,
each (_ >= "0" and _ <= "9") or _ = "." or _ = "," // keep only these characters
),
result = Text.Combine(filtered, ""), // combine the kept characters back
num = try Number.FromText(result) otherwise null // convert text to number
in
if num = null then
"ERROR, VERIFY DATA"
else if isMeters then // if it was meters, divide by 1000
num / 1000
else
num,
type any
}
}
)
|
|
|
| pus acum 6 zile |
|