Missing data when loading a view from Oracle

[smoortema]

I am loading data from a view in an Oracle database, using Power BI Desktop, and import mode. However, data is not the same if I run the query in Oracle Developer than when I load in in Power Query. Most of the data is missing in Power BI. I want to load a full year's data but the first date loaded in Power BI is July.

I copied the native query from Power Query and ran it in Oracle Developer, and it gave different results in Oracle Developer. Here, data was not missing. Native query basically the lists of the columns in my view, so there is not any transformation ther.

To debug, I modified the view to only contain data before July, and in this case, nothing at all is loaded in Power BI. Data before July is still visible in Oracle Developer.

Here is the M code of my query:

let
Source = Oracle.Database("dwh1_high", [HierarchicalNavigation=true]),
TEST2 = Source{[Schema="TEST2"]}[Data],
REP_COMPLAINTS_V = TEST2{[Name="REP_COMPLAINTS_V"]}[Data]
in
REP_COMPLAINTS_V 

 This is all very weird and seems to me like a bug. Any ideas about how to fix it?

[smoortema · Accepted solution]

After some debugging, research and several trials, I managed to find the root cause of the problem.

In the view, there is a function converting dates to day of week numbers. If using to_char(sysdate, 'D'), then the output of this function can be different according to where the query is coming from, and what NLS setting is set up in that server. So a different function should be used: TRUNC(sysdate) - TRUNC(sysdate, 'IW'), then you need to add +1 (if you want to have 1 for Monday and 7 for Sunday).

See more details here: https://stackoverflow.com/questions/14517761/using-oracle-sql-how-does-one-output-day-number-of-week...

[v-pgoloju]

Hi @smoortema,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution  it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @smoortema,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution  it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @smoortema,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @smoortema,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Here’s a summary of the suggestions to fix the issue:

Check View Logic: Ensure the view doesn’t use SYSDATE or session-based filters. use fixed date filters instead.

Check User Context: Verify if the data returned differs based on the user or session by comparing the Oracle user in SQL Developer vs Power BI.

Use Native SQL Query: Replace the default loading with a custom SQL query to bypass query folding:

Source = Oracle.Database("dwh1_high", [Query="SELECT * FROM TEST2.REP_COMPLAINTS_V"])

Check Regional Settings & Data Types: Make sure dates and data types are interpreted correctly without causing silent errors.

Ignore Preview Limits: Ensure you're not relying only on the preview in Power Query (it shows a limited number of rows).

Use Query Diagnostics: Enable diagnostics in Power BI to see what query is actually sent and what Oracle returns.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & Regards,
Prasanna kumar

[smoortema]

@bhanu_gautam Thanks. I am using import mode, I updated my original post to include this information.

I tried the buffer option, it did not change anything unfortunately.

[bhanu_gautam]

@smoortema If you are using Direct Query mode, try switching to Import mode to see if the issue persists. Direct Query mode can sometimes have limitations or issues that do not occur in Import mode.

Here is an example of how you might modify your M code to use Table.Buffer to disable query folding:

let
Source = Oracle.Database("dwh1_high", [HierarchicalNavigation=true]),
TEST2 = Source{[Schema="TEST2"]}[Data],
REP_COMPLAINTS_V = TEST2{[Name="REP_COMPLAINTS_V"]}[Data],
BufferedTable = Table.Buffer(REP_COMPLAINTS_V)
in
BufferedTable