Group by error being displayed when there is no error

[Anonymous]

Hi,

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

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??

[v-yuezhe-msft]

@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.


Regards,
Lydia

[Anonymous]

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

[v-yuezhe-msft]

@Anonymous ,

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

[Anonymous]

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}})

[v-yuezhe-msft]

@Anonymous ,

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

Reagrds,
Lydia

[Anonymous]

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

[v-yuezhe-msft]

@Anonymous ,

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

Regards,
Lydia