general question about transformations in Direct Query mode

[Jolyon]

Hello everyone,

I have a general question to data transformations in Direct Query mode.

1)is it possible to produce a relationship between Data model from CRM database(using Direct Query) and a local Excel table, if I want to make mapping of two columns?

2) Is it possible to rename the columns in my Data modell or does it influence somehow the original data in database?

3) add new columns/tables etc.?

Here I found:

"If you are getting data from a relational database, such as SQL Server or Oracle, you should never import a database table directly in your data model. The reason is that this creates a strong dependency between the physical data model and the report. Over time, certain changes to the database structure might corrupt an existing report. For example, renaming a column or a table, or changing the cardinality of a table, are all operations that require a correspondent change to the Power BI data model."

https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

thanks!

[v-yuezhe-msft · Accepted solution]

Hi @Jolyon,

I make a test that connect to SQL Server database via  DirectQuery option in Power BI Desktop and perform the actions you want to do.

Firstly, we are not able to import local Excel table to Power BI Desktop in the current PBIX file once you connect to SQL Server database via  DirectQuery option. You will get the following message that suggest you to switch from DirectQuery mode to import mode. Thus, in DirectQuery mode, it is impossible to produce a relationship between Data model from CRM database and a local Excel table.


Secondly, we can rename the columns in Power BI Desktop, it will not impact the original data in SQL Server database.

Thirdly, we are able to add new columns but we can’t add new table. For more information about using DirectQuery in Power BI Desktop, please refer to this article: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/ .

Thanks,
Lydia Zhang

[ashmozzart]

I tried to rename columns in the Report in DQ mode,It is renaming the columns but the data is not getting Refreshed.It is throwing Error like this when we refresh the data:

                  "This query contains transformations that cannot be used for a live connection"

[Jolyon]

ok, thanks!
But I can still use Measures?

[v-yuezhe-msft]

Hi @Jolyon,

Yes. You can create and use measures.

Thanks,
Lydia Zhang