Sharepoint Data with Oracle Data

[Maximous89]

Hello power bi sharks,

i kinda have a complex situation here,
let me explain please...

i have a formula that fire sql statments:

( Sql as text) as table =>

let

   Source = Value.NativeQuery(Oracle.Database(Target_Conn_str, [CommandTimeout = #duration(0,0,40,0), HierarchicalNavigation=true]),Sql)

in

    Source

 then i have queris like this:

 sharepoint_List = SQL_List,
    sql = "
SELECT * FROM ABC."& Target_Category_MarketSet &" WHERE DATAMART IN  (" & sharepoint_List & ")   
  "
, 
 Source = fnOracleNativeQuery(sql),
#"Merged Queries" = Table.NestedJoin(Source, {"SHORT_DESCR"}, MARKETSET_FILTER, {"SHORT_DESCR"}, "MARKETSET_FILTER", JoinKind.LeftOuter),
 #"Expanded MARKETSET_FILTER" = Table.ExpandTableColumn(#"Merged Queries", "MARKETSET_FILTER", {"MARKETSET_FILTER"}, {"MARKETSET_FILTER"})


in
    #"Expanded MARKETSET_FILTER"

Now we have tow sharepoint filer here the sharepoit_list and the table that i used it for join MARKETSET_FILTER that's as well a share point file,

now in desktop to make this work all i need to do to check in the privacy levels always ignore privacy level,

but on the service Ohhhhh.., it's very differenet story, 

first i create the gateway conn with privacy level of none but still didn't work, so i found i must buffer my list and so i did,

still didn't work, then i read i must use a staging query of the buffered list, still didn't work, 

i buffered even the buffer!

is there a way to make sharepoint file load first before oracle?!!!

that my SQL_List query and it's buffered from a another buffer :

let
    Source = List.Buffer(Tables),

    SQLList =
        Text.Combine(
            List.Transform(Source, each "'" & _ & "'"),
            ","
        )
in
    SQLList

Tables here as well is buffered in a seprate query that is buffered from source sharepoint file.

[v-saisrao-msft]

HI @Maximous89,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @Maximous89,

Have you had a chance to review the solution we shared by @lbendlin? If the issue persists, feel free to reply so we can help further.

Thank you.

[lbendlin · Accepted solution]

There are a few things going on here.

You are using Value.NativeQuery but without specifying your folding intent. That makes it ineffective.

You are specifying both a query and HierarchicalNavigation=True.  That doesn't go together, and you should set the navigation to false

You are combining an on-premise data source with a cloud data source.  This mandates that your cloud data source needs to be accessed via a gateway connection.  Your gateway needs to be configured accordingly.

You chose to ignore privacy settings. That is not recommended. Better set both to Organizational.

As to what gets executed first - that is up to Power Query to decide.  If you want to enforce things you may need to use Function.InvokeAfter() in a slightly quirky way.