Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have a column type text where some values are only numbers and start with many zeros 00000000000123.
Other values are text only or are combination of text, characters and numbers; sample:
Goal is to trim first zeros, but ONLY from values that contain only numbers (so e.g. 00E2 should keep zeros after transformation, but above 00000000123 should be 123).
I wast trying to duplicate column and change type of column to decimal number - then keep values from this column if there is no error, and from the original column if I get errors in the second column.
BUT some lines that contain text get transfered to number (these become zero):
Do you have a better idea to solve this or you know why these become zero?
Thank you in advance!
Solved! Go to Solution.
Hi @Pbix_is ,
Try this:
Table.AddColumn(Source, "Custom", each try Number.ToText(Number.From(Text.TrimStart([Column1],"0"))) otherwise [Column1], type text)
Hi @Pbix_is ,
Try this:
Table.AddColumn(Source, "Custom", each try Number.ToText(Number.From(Text.TrimStart([Column1],"0"))) otherwise [Column1], type text)
Thank you!! It does solve all that I described above!
These came up, however, so I think I will add condition if text contains "E" or "." keep first column.
Oh, forgot to mention: in Power Query Editor
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.