Workaround for Transformations Using Direct Query from Dataverse

[nigama]

I am trying to direct query from Dataverse and need to do certain transformations (create a calculated column and then unpivot a couple of columns to help with my visualizations). How do I do this? It is important that I use DirectQuery because I need real-time data display for my report that I will be publishing to my Power BI service. 

[v-sgandrathi]

Hi @nigama,

Has your issue been resolved?
If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

[v-sgandrathi]

Hi @nigama,

Thank you for your inquiry about using transformations in Power BI with DirectQuery mode.

DirectQuery mode has several limitations, as all transformations must be converted to SQL and run directly on the source system. This means features like calculated columns are not available within Power BI when using DirectQuery; their logic needs to be implemented in the source system (such as Dataverse). Additionally, unpivoting columns is generally not supported in DirectQuery mode and may cause queries to fail.

To address these limitations, it is advisable to use a Composite Model, which lets you combine Import and DirectQuery modes within a single dataset. You can import the tables that require transformations like calculated columns or unpivoting, while maintaining DirectQuery connections for other tables to ensure real-time data. Please note, unpivoting is only available for imported tables, not those using DirectQuery.

I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.

Thank you.

[nigama]

Thank you for the detailed information. I will try using a composite model or figure out to diplay my data the way I want without unpivoting. 

Since I need my matrix visual to get populated with the latest data in real time (I want the data to show up in my matrix as soon as the data gets populated), do you think that direct query is the right option? or is there any other way I could achieve this ? 

[Abhilash_P]

Yes Direct query is the right option to populate live data in Power BI,
Also you can performing transormations in backend source instead of PowerBI

[nigama]

It might not be feasable to perform transformations on my original datasource. 
I need to change the data type of one of my columns -> create a calculated column (by adding two of my existing columns ) -> unpivot few columns to use it for my visualization. 
I found a work around for the new column (added new column with some DAX) and unpivoting by creating a new table in my power BI desktop with some DAX.
But is there a way to work around the data type issue? 

[Abhilash_P]

Hi @nigama 
No, you cannot change the data type of a column in Power BI Desktop when using DirectQuery mode because In DirectQuery mode, Power BI does not store data locally. It sends queries directly to the underlying data source (SQL Server, Snowflake, etc.) every time you interact with visuals..
Below are the possible solutions
1.  Use a Native SQL Query in Power Query. Below is the sample SQL Query

SELECT 
    CAST(YourColumn AS VARCHAR(50)) AS YourColumn,
    OtherColumn,
    AnotherColumn
FROM 
    YourTable

[MasonMA]

@Abhilash_P 

did you tag the wrong person? anyways, even under DirectQurery you can still add a new column with DAX to change the old column's data type.

let's say using below DAX to convert a text column with numbers to actual numbers

NewNumberColumn = VALUE([TextColumn])

[MasonMA]

@nigama 

Hi, Composite Model would be an option if you need to create calculated columns. However, in all the composite models I used in the past I could only unpivot columns from tables based on Import mode, not those tables on DirectQuery. 

Not sure how you would like to use those unpivoted columns later in reporting, some visuals actually can be created without you unpivoting them.