Excel 2016 Power Query - Get data from Oracle data dictionary

Question

I've got an Excel 2016 spreadsheet set up with an Oracle db data source and I already have several queries set up in PowerQuery to get data from the tables in a specific schema and all is working well.

I now need to get some data from the data dictionary - I need to find the name of a trigger associated with a specific table in the schema - so I've set up a query to try to get data from user_triggers, but so far I've not been able to get it to work.

This is the query I have set up so far (SourceTableName is a reference to a named cell in the sheet to get the table name) :

let
    STN = Excel.CurrentWorkbook(){[Name="SourceTableName"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(STN,{{"Column1", type text}}),
    table_name = #"Changed Type"{0}[Column1],
    Source = Oracle.Database("MY_DB", [HierarchicalNavigation=true]),
    Schema = Source{[Schema="MY_SCHEMA"]}[Data],
    USER_TRIGGERS = Schema{[Name="USER_TRIGGERS"]}[Data]
in
    USER_TRIGGERS

This works perfectly fine for the other queries I already have set up as long as the table name is one of the tables in the schema, but referring to a data dictionary view as in the above example doesn't seem to work.

The error I get when trying to run the this query is:

Expression.Error: The key didn't match any rows in the table. Details: Key=Record Table=Table

Does anyone know if it's actually possible to get data from the data dictionary using powerquery and if it is what do I need to change to get it to work?

Thanks in advance for any help with this!

Cheers, Dave

Dave E Dave E · Accepted Answer · 2018-09-26T15:15:04

I've figured it out! Answering my own question in case it's useful for anyone else in the future

It's actually possible to specify an SQL query directly in the db connection line and you can include variable names from other parts of the query in the SQL, like so:

let
    STN = Excel.CurrentWorkbook(){[Name="SourceTableName"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(STN,{{"Column1", type text}}),
    table_name = #"Changed Type"{0}[Column1],
    upper_tn = Text.Upper(table_name),
    Triggers = Oracle.Database("MY_DB", [HierarchicalNavigation=true, Query="select trigger_name from user_triggers where table_name = '" & upper_tn & "'"])
in
    Triggers

Using the SQL query directly in this way seems to work fine for data dictionary views :)

Excel 2016 Power Query - Get data from Oracle data dictionary

1 Answers