I'm trying to convert text into a number so I can multiply it by 1.5 then use powerquery's Number.Round() function to keep 4 decimal places but I'm not able to keep any trailing zeros afterwards.
I was able to get around doing it mathematically just by manipulating the text with the Text.Pad functions to add zeros on the end but that's not working for all cases. Some cases there are no decimals at all kept and others it keeps 1 or 2 decimal places.
if [EarnCode] <> "02" then [Rate]
else if [Dept] = " 05" then 13.05
else Number.ToText(Number.Round(Number.FromText([Rate])*1.5,4))
I expect that the last else would catch all the departments that are "02" and then change their rate to be 1.5 times the amount then add 4 decimal places but in one case where the rate is 14.9 it's returning 22.35. It seems as though my Number.Round() function is not keeping the trailing zeros that I need for when I convert the number back into text. How would I go about keeping them?
I don't think you want to use the round function to get trailing zeros. Try this instead:
Number.ToText(Number.FromText([Rate])*1.5, "0.0000")
https://learn.microsoft.com/en-us/powerquery-m/number-totext