Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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???
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
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 ;
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |