this step results in a query that is not supported in directquery mode

[sheetalshettiga]

Hi all ,

I am using a direct Query mode to connect to oracle db with sql statement : select * from ORDERS

In the power query editor when I try to rename ORDER_ID column to ORDER_NO it shows me 'this step results in a query that is not supported in directquery mode'

I cannot add a new column or change data type aswell.But without sql query and choosing directquery mode I can perform all the transformations.Why is that?

Below is the MQUERY:

let
Source = Oracle.Database("localhost:1521/orcl", [HierarchicalNavigation=true, Query="select * from ORDERS"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"ORDER_ID", "ORDER_NO"}})
in
#"Renamed Columns"

[amitchandak]

@sheetalshettiga , Please double click on the column name and try. Does allow you to rename? If not you can do it Model/data view.

Direct query has some limitations : https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery#limitations-of-direct...

There are limitations on what column operations you can do. Some operation you can do, I have explored in this series

 https://www.youtube.com/watch?v=My0bLn9voo4&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA

[sheetalshettiga]

Hi @amitchandak 

Not able to do it in the power query editor .

In direct query mode there will be no data view. If I do it in Model view in the backend(Query editor how can I rename it)

Rename will only be done in the frontend(report view/model view)

[amitchandak]

@sheetalshettiga , Data view will there once you add any import table. Like I added a date table once.

But you should able to rename both in the model and Report view. (Any way, all three will behave in similar manner) for rename.

In the Model view, You have properties, where you can rename easily. Else double click on name in field pane or right click ->rename , should also do

[sheetalshettiga]

@amitchandak yes renaming can be done in fronteend but in powerquery editor it will not be renamed.

[amitchandak]

@sheetalshettiga , I am able to do the same for SQL server direct Query in power query. I double-clicked on the name when it was displayed in the table and then rename it. This is the script

let
    Source = Sql.Database("localhost\SQLEXPRESS", "local"),
    dbo_Customer = Source{[Schema="dbo",Item="Customer"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(dbo_Customer,{{"Age", "Age of customer"}})
in
    #"Renamed Columns"

I am dec 2020 version.

[sheetalshettiga]

@amitchandak  but not able to do it with the query statement as i mentioned in the question(in my mquery).

without query statement it can be done as you mentioned

[amitchandak · Accepted solution]

@sheetalshettiga , Yes, I tried a query and it does not Work. Seem like a limitation.

First log as an issue: https://community.powerbi.com/t5/Issues/idb-p/Issues

If rejected, log an idea: https://ideas.powerbi.com/ideas/

Let me know the links, I will vote