Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

Need Help

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 ;