Default query names for SQL sources include schema name

[ValentinLenkSBS]

What logic determines whether Power Query prepends the SQL Schema name to the automatic query names it generates, and (how) can I influence that logic?

Context: I'm querying data from an SQL source, where all the queries intended for end user consumption are held in the schema "consumption". Whenever I build a model from that source by selecting the relevant tables and loading them, PQ automatically appends that schema name to the query.

Ex.: I want to load Order, Item, Person and Country. I select the Views "Orders", "Items", Person" and "Country". Because they're all located in the SQL DB Schema "consumption", the Navigator displays them all as "consumption.Orders" etc. Upon loading them (or transforming them in PQ Editor), they are each loaded as "consumption Orders" etc.

I can leave those prefixes in, which leads to confused users asking me what that "consumption" is about, or I can manually remove that prefix from each and every query, which is tedious.

Recently, I accessed another data source, where I query against (raw physical) Database Tables held in the schema "dbo". I noticed that, for those tables, the prefix "dbo" is omitted and the queries are automatically named "Currency", "Account" etc. instead of "dbo Currency". This implies that there is a logic to prepending or omitting those schema prefixes.

Does that logic extend beyond "dbo is omitted, everything else isn't"? Is there any way to influence that?

Alternatively, is there a way to automatically rename every query to drop that prefix (aside from changing the schema to dbo, which I don't have the leverage for)?

[lbendlin]

If you don't like the auto generated SQL you can always use Value.NativeQuery to craft your own.

[ValentinLenkSBS]

Thanks for your reply. The auto generated SQL isn't my point, nor is the actual content of the queries.

I'm talking about the auto generated Query (and resulting loaded Table) Names.

Example Scenario:

1. I select an SQL data source named "SalesData"

2. I select the tables "Sale", "Product" and "Area" from that source.

3. Power Query generates one query for each of these tables

4. Power Query names those queries after the respective table or view, i.e. "Sale", "Product", "Area"

So far, this is expected and intended.

Now suppose the source database holds these tables in a database schema "consumption", a decision I have no control over. The table selection navigator then displays them as "consumption.Sale", "consumption.Product" and "consumption.Area". More critically, Step 4 becomes:

4. Power Query names those queries after the respective table or view, i.e. "consumption Sale", "consumption Product", "consumption Area"

My questions here are:

Is my understanding "dbo is omitted, everything else is not" correct?

Is there a way to affect that?

Is there a way to bulk rename these queries to drop the schema prefix?

[lbendlin]

To hide the schema from the original source selection step you can disable hierarchical navigation.

Not sure what the benefit is though - the query will still have to contain the schema unless it is the default schema.

[ValentinLenkSBS · Accepted solution]

Solution for future readers: The solution is enabling hierarchical navigation when adding a data source, which is turned off by default and can be selected in that first dialog (i.e. not when later accessing that same source through "Recent Sources").

In the advanced options, below the Query text field, there are a number of checkboxes, one of which is "Navigate using full hierarchy".

This will require you to navigate to and expand your desired schema, but will both display the table names without the "Schema."-Prefix and accordingly generate query names without the "Schema " prefix.

----

I don't care about the technical details of the query. My issue was the Query Name. From a presentation perspective "Sale" is more concise and reasonable than "consumption Sale". The "consumption" prefix is irrelevant visual clutter.

Your advice is the exact opposite of the solution: hierarchical navigation is disabled by default, which causes the issue in question in the first place. Power Query's behaviour is inconsistent here, in that its name generation logic differs based on a navigation setting, with the default leading to visual clutter.

However, usability is not the topic of this question, and my question has indirectly been answered. Thank you for your time.