Forum Discussion
Workaround for Transformations Using Direct Query from Dataverse
Even under DirectQurery you can still convert it to Composite mode and 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])Hi v-sgandrathi ,
Thank you for all the help. I was able to make things work on my end at the moment. And I am exploring further before I can confirm.
Here's what I have done based on the suggestions I recieved.
After connecting to my data (DirectQuery from Dataverse) I have added a new column in my original table with a DAX query.NewColumn = Table[Column1] + Table[Column2]
After adding the column to orignal table I have all the required information to add to my new unpivoted table. I used DAX to created an unpivoted version of my original table.
Here's an exampleUnpivotedTable = UNION( SELECTCOLUMNS( YourTableName, "Column1", YourTableName[Column1], "Metric", "Metric1", "Value", YourTableName[Metric1Column] ), SELECTCOLUMNS( YourTableName, "Column1", YourTableName[Column1], "Metric", "Metric2", "Value", YourTableName[Metric2Column] ), SELECTCOLUMNS( YourTableName, "Column1", YourTableName[Column1], "Metric", "Metric3", "Value", YourTableName[Metric3Column] ) )
Coming to converting the data type I tried a modified version of MasonMA 's approach and I was able to create a new column which converts the data from Text to Int in my case.NewNumberColumn = INT(VALUE([TextColumn]))Even though I was able to successfully create a column, I was not able to use this column to my visualization or to perform any other query operations without getting an error. For now I am sticking to changing the data type in the source itself. I am still looking into this but as soon as I figure it out I will update here.
Thank you so much for all the support!
EDIT: I could solve all my issues with a lot of DAX measures. I didnt have to create anything except for few dummy tables that are not related to any of my data sources.
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 ?
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
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?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 QuerySELECT CAST(YourColumn AS VARCHAR(50)) AS YourColumn, OtherColumn, AnotherColumn FROM YourTableEven under DirectQurery you can still convert it to Composite mode and 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])