Error on Data Load - OLE DB or ODBC error: [DataSource.Error] Date-aggregate functions...

[jdballard30]

I am getting the error "OLE DB or ODBC error: [DataSource.Error] Date-aggregate functions can only be selected in grouped queries." when loading a table in my model. It only has one date field in it, and on a whim, I tried changing it to text, but I'm still getting the same error. The preview loads fine with "refresh preview" when I'm creating the model, but as soon as I "Close & Apply" it loads about 56,000 records and then gives me the above error message.

I even checked the two tables that it is based on (the table is created by appending two tables), and they refresh fine. It's only this table that returns the error.

[akfir]

i am facing the same issue now but within dataflow. what can be the solution?!?? i am frustrated...

[v-lid-msft]

Hi @jdballard30 ，

Have you tried to change the option in buttom of Power Query Editor to "Column Profiling based on entire data set" ? If it does not work, could you please share the M Query functions you used in the error table if it does not contain any confidential information? 

Please don't have any Confidential Information or Real data in your reply.

Best regards,

[jdballard30]

I was able to get it to work, but the solution was a bit painful, and it really isn't a solution. I copied each Power Query script over to a new Power BI file, one by one, except for a few that I didn't need. When all was said and done, the new file, which has the exact same Power Query load statements (since I copied/pasted) worked, but the old file won't. Just to be certain it wasn't one of the other tables I didn't need and bring over, I deleted them from the original. It still didn't work.

I have no idea what the issue is in the original file.

[v-lid-msft]

Hi @jdballard30 ,

Sorry for that we did not found an effective solution, it seems there are something broken in the origin pbix file, We apologize for any inconvenience this issue have caused.  If you have any other questions about this scenario, please kindly ask here and we will try to resolve it.

Best regards,