Unpivot the star schema table in direct query mode

[keondopark]

Hi,

I am now trying to build a table using data from SQL Azure database, connected in direct query mode

My database used to be flat database, without any dimension tables and I could unpivot the data.

Then, I changed the database to have star schema and the query editor says "This step results in a query that is not supported in Direct Query mode".

Is unpivot not supported in direct query mode, if I use star schema table?

Thanks

[Seth_C_Bauer]

@keondopark Are you writting a query, or just connecting to the table? I assume writing a query.. have you tried to build the logic in your DB in a view and just connecting to that?

[keondopark]

Hi

I am trying to write a query to unpivot my table in Powe BI Query editor

In other words, my table looks like this

DimA   DimB   DimC   Var1 Var2  Var3

a          b          c          10     20     30 

a          b          d          100   200   300

And I want to unpivot this table as bleow

DimA   DimB   DimC   VarName Value 

a          b          c          Var1        10    

a          b          c          Var2        20

a          b          c          Var3        30

a          b          d          Var1        100    

a          b          d          Var2        200

a          b          d          Var3        300

I want to unpivot in Power BI, because it gives me more flexibilities.. if it is not supported I should try to do it in DB.

[Seth_C_Bauer]

@keondopark You can Transpose your query with advanced editor, however that action will result in changing the connection to "Import".

Put another way, that action is not supported in Direct Query, but you could do it if you imported the data. If you want to stay in Direct Query mode, than you will need to do this in the database.

[keondopark]

I deleted the relations after import the fact table, unpivot and create relationship again in Power BI. 

It needs some manual jobs, but it is working anyway.