cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelH Regular Visitor
Regular Visitor

Two power query steps dont work together

I have two steps that both work when put in the query editor by themselves, however if I use them both they do not work and give me this error instead: 

DataSource.Error: Oracle: ORA-01013: user requested cancel of current operation
Details:
DataSourceKind=Oracle
DataSourcePath=edwp2.airnz.co.nz
Message=ORA-01013: user requested cancel of current operation
ErrorCode=-2147467259

 

 

One step is a Group By,

= Table.Group(#"Removed Columns", {"SNAPSHOT_DATE_KEY", "FINANCIAL_MONTH_KEY", "LEG_NON_DIR_AIRPORT_PAIR_KEY", "SUB_ROUTE_KEY", "OPERATING_AIRLINE_COMPANY_KEY", "OPERATING_AIRLINE_KEY", "TICKETING_AIRLINE_KEY", "ACJ_ORIGIN_DESTINATION_KEY", "PNR_CREATION_COUNTRY_GROUP_KEY", "STAR_SERVICE_CLASS_KEY", "BOOKING_CLASS_KEY"}, {{"RPK_ACTUAL_LY", each List.Sum([RPK_ACTUAL_LY]), type number}, {"RPK_TY", each List.Sum([RPK_TY]), type number}, {"RPK_LY", each List.Sum([RPK_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_ACT_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}})

 

The next is a datetime filter as follows: 

 = Table.SelectRows(#"Grouped Rows", each [OPERATING_AIRLINE_KEY] = 226 and [SNAPSHOT_DATE_KEY] >= Date.Year(Date.AddWeeks(DateTime.LocalNow(), -16))*10000+Date.Month(Date.AddWeeks(DateTime.LocalNow(), -16))*100+Date.Day(Date.AddWeeks(DateTime.LocalNow(), -16))
or
[SNAPSHOT_DATE_KEY] <= Date.Year(Date.AddWeeks(DateTime.LocalNow(), -48))*10000 +Date.Month(Date.AddWeeks(DateTime.LocalNow(), -48))*100 +Date.Day(Date.AddWeeks(DateTime.LocalNow(), -48))
and [SNAPSHOT_DATE_KEY] > Date.Year(Date.AddWeeks(DateTime.LocalNow(), -52))*10000+Date.Month(Date.AddWeeks(DateTime.LocalNow(), -52))*100+Date.Day(Date.AddWeeks(DateTime.LocalNow(), -52))
and [OPERATING_AIRLINE_KEY] = 226)

 

 

Whats happening???

3 REPLIES 3
Community Support Team
Community Support Team

Re: Two power query steps dont work together

Hi @MichaelH ,

 

Could you please check the column names in step2 matches the ones in step1. Or try to select rows after gourp by operation.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MichaelH Regular Visitor
Regular Visitor

Re: Two power query steps dont work together

Hi @v-frfei-msft I have triple checked the names in both - and they align.

Have also put the grouping first followed by the filter select statement. 

 

Neither have improved the issue just yet. It seems to try and load the query for say 5 minutes before spitting out the error

MichaelH Regular Visitor
Regular Visitor

Re: Two power query steps dont work together

Additionally - have extracted the SQL sent to Oracle and running it in a standard SQL client returns results just fine:

select "SNAPSHOT_DATE_KEY",
"FINANCIAL_MONTH_KEY",
"LEG_NON_DIR_AIRPORT_PAIR_KEY",
"SUB_ROUTE_KEY",
"OPERATING_AIRLINE_COMPANY_KEY",
"OPERATING_AIRLINE_KEY",
"TICKETING_AIRLINE_KEY",
"ACJ_ORIGIN_DESTINATION_KEY",
"PNR_CREATION_COUNTRY_GROUP_KEY",
"STAR_SERVICE_CLASS_KEY",
"BOOKING_CLASS_KEY",
"RPK_ACTUAL_LY",
"RPK_TY",
"RPK_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_ACT_LY",
"REV_PAX_COUNT_LY",
"REV_PAX_COUNT_TY"
from (select "_"."SNAPSHOT_DATE_KEY",
"_"."FINANCIAL_MONTH_KEY",
"_"."LEG_NON_DIR_AIRPORT_PAIR_KEY",
"_"."SUB_ROUTE_KEY",
"_"."OPERATING_AIRLINE_COMPANY_KEY",
"_"."OPERATING_AIRLINE_KEY",
"_"."TICKETING_AIRLINE_KEY",
"_"."ACJ_ORIGIN_DESTINATION_KEY",
"_"."PNR_CREATION_COUNTRY_GROUP_KEY",
"_"."STAR_SERVICE_CLASS_KEY",
"_"."BOOKING_CLASS_KEY",
"_"."RPK_ACTUAL_LY",
"_"."RPK_TY",
"_"."RPK_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_ACT_LY",
"_"."REV_PAX_COUNT_LY",
"_"."REV_PAX_COUNT_TY"
from
(
select "rows"."SNAPSHOT_DATE_KEY" as "SNAPSHOT_DATE_KEY",
"rows"."FINANCIAL_MONTH_KEY" as "FINANCIAL_MONTH_KEY",
"rows"."LEG_NON_DIR_AIRPORT_PAIR_KEY" as "LEG_NON_DIR_AIRPORT_PAIR_KEY",
"rows"."SUB_ROUTE_KEY" as "SUB_ROUTE_KEY",
"rows"."OPERATING_AIRLINE_COMPANY_KEY" as "OPERATING_AIRLINE_COMPANY_KEY",
"rows"."OPERATING_AIRLINE_KEY" as "OPERATING_AIRLINE_KEY",
"rows"."TICKETING_AIRLINE_KEY" as "TICKETING_AIRLINE_KEY",
"rows"."ACJ_ORIGIN_DESTINATION_KEY" as "ACJ_ORIGIN_DESTINATION_KEY",
"rows"."PNR_CREATION_COUNTRY_GROUP_KEY" as "PNR_CREATION_COUNTRY_GROUP_KEY",
"rows"."STAR_SERVICE_CLASS_KEY" as "STAR_SERVICE_CLASS_KEY",
"rows"."BOOKING_CLASS_KEY" as "BOOKING_CLASS_KEY",
sum("rows"."RPK_ACTUAL_LY") as "RPK_ACTUAL_LY",
sum("rows"."RPK_TY") as "RPK_TY",
sum("rows"."RPK_LY") as "RPK_LY",
sum("rows"."SALES_DEAL_VALUE_ACT_LY") as "SALES_DEAL_VALUE_ACT_LY",
sum("rows"."SALES_DEAL_VALUE_LY") as "SALES_DEAL_VALUE_LY",
sum("rows"."SALES_DEAL_VALUE_TY") as "SALES_DEAL_VALUE_TY",
sum("rows"."SALES_REV_PAX_COUNT_ACT_LY") as "SALES_REV_PAX_COUNT_ACT_LY",
sum("rows"."SALES_REV_PAX_COUNT_LY") as "SALES_REV_PAX_COUNT_LY",
sum("rows"."SALES_REV_PAX_COUNT_TY") as "SALES_REV_PAX_COUNT_TY",
sum("rows"."REV_PAX_COUNT_ACTUAL_LY") as "REV_PAX_COUNT_ACT_LY",
sum("rows"."REV_PAX_COUNT_LY") as "REV_PAX_COUNT_LY",
sum("rows"."REV_PAX_COUNT_TY") as "REV_PAX_COUNT_TY"
from
(
select "SNAPSHOT_DATE_KEY",
"FINANCIAL_MONTH_KEY",
"LEG_NON_DIR_AIRPORT_PAIR_KEY",
"SUB_ROUTE_KEY",
"OPERATING_AIRLINE_COMPANY_KEY",
"OPERATING_AIRLINE_KEY",
"STAR_SERVICE_CLASS_KEY",
"ACJ_ORIGIN_DESTINATION_KEY",
"PNR_CREATION_COUNTRY_GROUP_KEY",
"BOOKING_CLASS_KEY",
"REV_PAX_COUNT_TY",
"REV_PAX_COUNT_LY",
"REV_PAX_COUNT_ACTUAL_LY",
"RPK_TY",
"RPK_LY",
"RPK_ACTUAL_LY",
"SALES_DEAL_VALUE_TY",
"SALES_DEAL_VALUE_LY",
"SALES_DEAL_VALUE_ACT_LY",
"SALES_REV_PAX_COUNT_TY",
"SALES_REV_PAX_COUNT_LY",
"SALES_REV_PAX_COUNT_ACT_LY",
"TICKETING_AIRLINE_KEY"
from "EDR"."FACT_RES_SUM_AGENT" "$Table"
) "rows"
group by "SNAPSHOT_DATE_KEY",
"FINANCIAL_MONTH_KEY",
"LEG_NON_DIR_AIRPORT_PAIR_KEY",
"SUB_ROUTE_KEY",
"OPERATING_AIRLINE_COMPANY_KEY",
"OPERATING_AIRLINE_KEY",
"TICKETING_AIRLINE_KEY",
"ACJ_ORIGIN_DESTINATION_KEY",
"PNR_CREATION_COUNTRY_GROUP_KEY",
"STAR_SERVICE_CLASS_KEY",
"BOOKING_CLASS_KEY"
) "_"
where ("_"."OPERATING_AIRLINE_KEY" = 226 and "_"."OPERATING_AIRLINE_KEY" is not null)
and "_"."SNAPSHOT_DATE_KEY" >= 20181123 or ("_"."SNAPSHOT_DATE_KEY" <= 20180413 and "_"."SNAPSHOT_DATE_KEY" > 20180316)
and ("_"."OPERATING_AIRLINE_KEY" = 226 and "_"."OPERATING_AIRLINE_KEY" is not null))
"$Paged"
where rownum <= 1000 ;