Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 ID | Reading Age |
1 | 11 years, 2 months |
2 | 13 years, 2 months |
3 | 14 years, 7 months |
4 | 17 years, 0 months |
5 | 15 years, 6 months |
6 | 13 years, 2 months |
7 | 16 years, 5 months |
8 | 14 years, 0 months |
9 | 13 years, 8 months |
10 | 15 years, 6 months |
11 | 10 years, 5 months |
12 | 5 years, 0 months |
13 | 17 years, 0 months |
14 | 15 years, 10 months |
15 | 17 years, 0 months |
16 | 17 years, 0 months |
17 | 17 years, 0 months |
18 | 16 years, 9 months |
19 | 17 years, 0 months |
Solved! Go to 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
Select slicer, here is my preview:
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.
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
Select slicer, here is my preview:
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.
@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
Proud to be a Super User! |
|
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |