Power Query calculation

[hcze]

Dear Power BI gurus,

I have a list that contain only one value of current year. I want to create a column for prior year.

Here is the code:

PriorYear = Character.ToNumber(List.First(CurrentFinYear)) - 1,
AddFinYear = Table.AddColumn(ChangedType,"Financial Year",each PriorYear, type text),

It returns error:

DataFormat.Error: We couldn't convert to Number.
Details:
2020

Eliminating "Character.ToNumber" function returns: Expression.Error: We cannot apply operator - to types Text and Number.

Helps appreciated.

Thanks,

hcze

[Fowmy · Accepted solution]

@hcze 

Hi, Replace Character.ToNumber to Number.From

PriorYear = Number.From  (List.First(CurrentFinYear)) - 1

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[edhans]

Hi @hcze ,
@Fowmy is correct. Character.Number is like the CODE() function in Excel. So

= Character.ToNumber("A") <- Power Query
= CODE("A")               <- Excel

Both return 65, the ASCII value of the letter A. It is rarely used in Power Query. Not never, but not too common. The Number.From and Date.From functions are much more common. 

Please mark his answer as the solution if it fits your needs. If not, post back with more details on remaining issues.

Kudos for assisting are appreciated! Have a good weekend.