Workaround for Transformations Using Direct Query from Dataverse

Abhilash_P 

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 example 

UnpivotedTable = 
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.