I have tried both Power Query with a Data Model and creating pivot tables embedded in the workbook to populate a dashboard in Excel, but neither of them lets me group the referral dates after I update the data source; suddenly, my formula goes from =GETPIVOTDATA("Organ Group",RefEnd!$A$3,"Ref End",MONTH(G1),"Ref End Reason","Evaluated","Years2",YEAR(G1)) to #REF?, and the neatly grouped months in the pivot table are separated into days, giving an error that they cannot be grouped. What will allow me to update the source without breaking those formulas?
Hi Sandra,
It sounds like the problem is that the source data is not in the correct date format to allow the PivotTable to group the dates. This is the reason for the error saying the dates cannot be grouped.
You can fix the date format using Power Query by setting the data type, or one of these methods in Excel: https://www.myonlinetraininghub.com/6-ways-to-fix-dates-formatted-as-text-in-excel
Mynda
