DataFlow - Natique Oracle query using Incremental refresh - Error 500

[Erebe]

Hello,

I'm trying to create a new Dataflow referencing an Oracle database.

I would like ot use a native query to perform some tasks on the source side.

I have no problem to connect to my DB (I have several dataflows that are refreshed).

But when I'm using native query, I cannot save my dataflow. I have this error:

Activity ID: 8f0df613-49aa-4e5b-950f-14dc863bac92
Request ID: 19039a32-0188-df35-a78a-a2c880ec3bd4
Correlation ID: a09cceb9-0024-12c3-c376-5bc09d590cec
Status code: 500
Time: Fri Mar 25 2022 10:22:53 GMT+0100 (Central European Standard Time)
Service version: 13.0.17904.42
Client version: 2203.2.10453-train
Cluster URI: https://wabi-france-central-a-primary-redirect.analysis.windows.net/

Here is my query:

getDateKey

let
    Source = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)
in
    Source

Basic query

let
    startDate = getDateKey(RangeStart),
    endDate = getDateKey(RangeEnd),
    query = "select test_date, load_date from csn where to_number(to_char(load_date,'yyyymmdd')) > " & Text.From(startDate) & " and to_number(to_char(load_date,'yyyymmdd')) <= " & Text.From(endDate),
    Source = Oracle.Database("my gateway information", [Query = query, HierarchicalNavigation = true]),
    #"CSN-4C4F41445F44415445-autogenerated_for_incremental_refresh" = Table.SelectRows(Source, each DateTime.From([LOAD_DATE]) >= RangeStart and DateTime.From([LOAD_DATE]) < RangeEnd)
in
    #"CSN-4C4F41445F44415445-autogenerated_for_incremental_refresh"

In the editor, I manage to have a preview of my query:

I'm using a gateway to connect to my DB.

I have a pro license, but I'm not administrator of the BI server. So I don't have access to any log.

Thanks in advance for your help.

[v-yanjiang-msft]

Hi @Erebe ,

Please refer to this similar issue:  Can't save Dataflow due to internal error - Microsoft Power BI Community

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[lbendlin]

You may be overcomplicating things. Use Date.ToText() directly with RangeStart and RangeEnd, directly in your native query. (Shame that dataflows insist on messing with the query step order, but whatever)

NOTE:  You have a logical conflict between your native query   ( greater than start, less or equal to end) and the incremental refresh (greater or equal to RangeStart, less than RangeEnd)

[Erebe]

Hello. Thank you for your answer.

I find it difficult the date/datetime insertion in native query.

The simple Date.ToText() doesn't work for the rangeStart/rangeEnd parameters:

We cannot convert the value #datetime(1, 1, 1, 0, 0, 0) to type Date.

Indeed I have a logical conflict. Fixed it, but I still have the issue.

[lbendlin]

Use DateTime.ToText instead.

BUT. Do not use a date like 1,1,1 - use only dates that appear in your data, or something close.  Using "moon dates" is bad practice.

[Erebe]

Hello,

Thank you for your answer.

After some modifications, I am able to use the DateTime conversion:

let
    query = "select * from csn where load_date >= to_date('" & DateTime.ToText(RangeStart) & "', 'MM/DD/YYYY HH:MI:SS AM') and load_date < to_date('" & DateTime.ToText(RangeEnd) & "', 'MM/DD/YYYY HH:MI:SS AM')",
    Source = Oracle.Database("xxxxxxxxxxxxxxxx", [Query = query, HierarchicalNavigation = true]),
    #"CSN-4C4F41445F44415445-autogenerated_for_incremental_refresh" = Table.SelectRows(Source, each DateTime.From([LOAD_DATE]) >= RangeStart and DateTime.From([LOAD_DATE]) < RangeEnd)
in
    #"CSN-4C4F41445F44415445-autogenerated_for_incremental_refresh"

But I'm still facing the same issue when I validate the dataflow:

The dataflow takes some time before raising the error. Could it be due to a timeout or something like this?

[lbendlin]

You are supposed to specify the format as part of DateTime.ToText(), not outside of it.  NOTE:  AM/PM is formatted as tt.