Hello. I am a first time poster and new to adding data to the data model when creating a pivot table.
I need to produce a weekly broadsheet summary of students' lesson attendance marks (which are text characters such as '/' and 'N') and I am limited to exporting the data from my MIS as a list. There are up to 25 rows of data per student (5 lessons per day for Mon-Fri). There are about 1200 students on roll at my school, so the weekly export is c30000 rows.
I have managed to do this successfully for the past few weeks, but have found that sometimes the 'concatenatex' measure I add to show the actual marks won't go into the 'Sum Values' section of the pivot table. There is no error message and Excel doesn't hang or crash. The 'Reading data' bar appears to progress as normal but then stops just before it completes, before disappearing without the measure being placed in the Values section. Other times it goes in fine. In the attached example I have stripped the initial table down to only 2 students (50 rows) and, as you can see, it works fine.
I cannot find any information online about what might be causing this and have spent three weeks experimenting to see if I can find any consistent factor when it does not work. I am currently working from home and use a remote desktop which connects me on any given occasion to any of about 30 different PCs at work. My first thought was that this is something to do with insufficient processing capacity on some of them and after several failed attempts on different remote logins, I tried it directly on my own laptop where it worked fine. However, as the weeks have gone on, I can't even get it to work on my own laptop, unless I limit it to just a handful of rows. Could it be RAM or a cache or something? I am using Excel 365.
I really need to get it fixed and am desperately hoping that someone can shed some light on what is going wrong and how I fix it.
Many thanks.
Bookmarks