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

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

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

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 ;

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 107 members 1,529 guests
Please welcome our newest community members: