cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hcze
Helper I
Helper I

Power Query calculation

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

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors