cancel
Showing results for 
Search instead for 
Did you mean: 

Group by error being displayed when there is no error

Hi,

 

I am performing a group by function and this error is given:

 

Group by error.PNG

This appears to be "occurs when the GROUP BY clause does not contain all the expressions in the SELECT clause".

I have double triple and quadruple checked and everything is in the group by function 100% (either as a group by or a column being grouped).

I have also tried deleting step and rebuilding.

 

The thing is - it loads fine to PowerBI ... I just can't view it in the Query Editor!

I also wonder if query folding is not being performed because I can't view the native query.. (which is more of an issue)

 

Any ideas??

Status: Needs Info
Comments
Moderator

@MichaelH,

What version of Power BI Desktop do you use and what specific SQL statement do you enter in Power BI Desktop? Could you please share sample data of your oracle table here?

I make a test using group by expression in Power BI Desktop Feb version, everything works well.
1.PNG2.PNGCapture.PNG

Regards,
Lydia

Moderator
Status changed to: Needs Info
 
Regular Visitor

Hi @v-yuezhe-msft I am not creating the SQL myself, this is using the 'Group By' header in the query editor

Moderator

@MichaelH ,

I am unable to reproduce this issue on my side, what is your Power BI Desktop version and could you please share data of your original oracle table so that I can test?

Regards,
Lydia

Regular Visitor

I am on version: Version: 2.65.5313.1381 64-bit (December 2018) 

 

I can't obviously share my Oracle table but here are the two M Querys:


= Table.SelectColumns(#"Operated flights only",{
"SNAPSHOT_DATE_KEY", "FINANCIAL_MONTH_KEY", "LEG_NON_DIR_AIRPORT_PAIR_KEY", "SUB_ROUTE_KEY", "OPERATING_AIRLINE_KEY", "TICKETING_AIRLINE_KEY", "ACJ_ORIGIN_DESTINATION_KEY", "PNR_CREATION_COUNTRY_GROUP_KEY", "STAR_SERVICE_CLASS_KEY","BOOKING_CLASS_KEY", "OPERATING_AIRLINE_COMPANY_KEY", "RPK_TY", "RPK_LY", "RPK_ACTUAL_LY", "SALES_DEAL_VALUE_ACT_LY", "SALES_DEAL_VALUE_LY", "SALES_DEAL_VALUE_TY", "SALES_REV_PAX_COUNT_ACT_LY", "SALES_REV_PAX_COUNT_LY", "SALES_REV_PAX_COUNT_TY", "REV_PAX_COUNT_ACTUAL_LY", "REV_PAX_COUNT_LY", "REV_PAX_COUNT_TY"})

 

Next step with error ORA-00979: not a GROUP BY expression ErrorCode=-2147467259

= Table.Group(#"Removed Other Columns", {"SNAPSHOT_DATE_KEY", "FINANCIAL_MONTH_KEY", "LEG_NON_DIR_AIRPORT_PAIR_KEY", "SUB_ROUTE_KEY", "OPERATING_AIRLINE_KEY", "TICKETING_AIRLINE_KEY", "ACJ_ORIGIN_DESTINATION_KEY", "PNR_CREATION_COUNTRY_GROUP_KEY", "STAR_SERVICE_CLASS_KEY","BOOKING_CLASS_KEY", "OPERATING_AIRLINE_COMPANY_KEY"}, {{"RPK_TY", each List.Sum([RPK_TY]), type number}, {"RPK_LY", each List.Sum([RPK_LY]), type number}, {"RPK_ACTUAL_LY", each List.Sum([RPK_ACTUAL_LY]), type number}, {"SALES_DEAL_VALUE_ACT_LY", each List.Sum([SALES_DEAL_VALUE_ACT_LY]), type number}, {"SALES_DEAL_VALUE_LY", each List.Sum([SALES_DEAL_VALUE_LY]), type number}, {"SALES_DEAL_VALUE_TY", each List.Sum([SALES_DEAL_VALUE_TY]), type number}, {"SALES_REV_PAX_COUNT_ACT_LY", each List.Sum([SALES_REV_PAX_COUNT_ACT_LY]), type number}, {"SALES_REV_PAX_COUNT_LY", each List.Sum([SALES_REV_PAX_COUNT_LY]), type number}, {"SALES_REV_PAX_COUNT_TY", each List.Sum([SALES_REV_PAX_COUNT_TY]), type number}, {"REV_PAX_COUNT_ACTUAL_LY", each List.Sum([REV_PAX_COUNT_ACTUAL_LY]), type number}, {"REV_PAX_COUNT_LY", each List.Sum([REV_PAX_COUNT_LY]), type number}, {"REV_PAX_COUNT_TY", each List.Sum([REV_PAX_COUNT_TY]), type number}})

Moderator

@MichaelH ,

You can share dummy data of your table here. I am unable to reproduce this issue using my own table.

Reagrds,
Lydia

Regular Visitor

Its connected to an Oracle table - makes it a little hard to share data!

Moderator

@MichaelH ,

You can export the data from Oracle to Excel or Csv file, then share the file here.

Regards,
Lydia