Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
duesouth
Helper I
Helper I

Age in Years Months

I work in a school.  We test the pupils on their reading ages.  This is stored in our database/management information system in a odd format - e.g. 11/2 is 11 Years, 2 months; 15/10 is 15 years, 10 months.


I'm trying to get these reading ages into Power BI.  I've got a report out of our database, but am struggling to get the data into a table.  I tried splitting the columns in Excel first and having them in the format:  11 years, 2 months but Power BI is picking that up as a text field.  I can't seem to change the format in power query - I just get errors on the whole column.  I'd like Power BI to give me average reading ages etc.  Any ideas how I can get this into Power BI in a useable format?  Does it need to be in a different format in Excel first?  I've spent this morning searching for solutions, but can't find anything.  Would appreciate any help!

 

Pupil IDReading Age
111 years, 2 months
213 years, 2 months
314 years, 7 months
417 years, 0 months
515 years, 6 months
613 years, 2 months
716 years, 5 months
814 years, 0 months
913 years, 8 months
1015 years, 6 months
1110 years, 5 months
125 years, 0 months
1317 years, 0 months
1415 years, 10 months
1517 years, 0 months
1617 years, 0 months
1717 years, 0 months
1816 years, 9 months
1917 years, 0 months
1 ACCEPTED SOLUTION

Hi, @duesouth 
I'm using your original table and first create new columns to separate year and month:

 

Years = LEFT([Reading Age], SEARCH(" ", [Reading Age])-1)
Months = MID([Reading Age], SEARCH(",", [Reading Age])+2, LEN([Reading Age])-SEARCH(",", [Reading Age])-8)

 

Then convert them to Whole number and put it into the view as an Average

vyohuamsft_1-1719195933076.png

 

Select slicer, here is my preview:

vyohuamsft_0-1719195382988.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
duesouth
Helper I
Helper I

I've got the data into Power BI.  Years in one column, months in another.  The trouble when I average for a year group the average is 12.41 years and 4.28 months.  The .41 years would need to be added to the months, if you are with me?  Is there no way to store years and months together in one column - and then an average would come out something like 12 years 7 months?

Hi, @duesouth 
I'm using your original table and first create new columns to separate year and month:

 

Years = LEFT([Reading Age], SEARCH(" ", [Reading Age])-1)
Months = MID([Reading Age], SEARCH(",", [Reading Age])+2, LEN([Reading Age])-SEARCH(",", [Reading Age])-8)

 

Then convert them to Whole number and put it into the view as an Average

vyohuamsft_1-1719195933076.png

 

Select slicer, here is my preview:

vyohuamsft_0-1719195382988.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

bhanu_gautam
Super User
Super User

@duesouth , You can try to split this Age column in excel using text to column function and convert the datatype into number and than try to load it into Power BI then 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Thanks for the reply.  Have two separate columns - one for year and one for month?  Would Power BI then average?  E.g. if I had

13 years, 1 month

13 years, 3 months

Would Power BI then give me an average for that "group" of 13 years, 2 months?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.