Forum Discussion
Helper IUnpivot columns while connected with Direct Query
Hi all,
Is there a way to unpivot columns in power query while the data connected is using direct query?
I have seen an example (powerbi - Is it possible to unpivot in Power BI using DAX? - Stack Overflow) using UNION but my case is not related to calendar tables. See image below where my ID column has a duplicate for almost every row. That is the one I am right clicking to unpivot other columns
The issue here is that because of direct query this cannot be done the usual way because it pops an error code like so:
Is there a way around this?
Appreciate any suggesstions AlexisOlson amitchandak
Here's a simpler example.
Do this:
to get this:
8 Replies
- AlexisOlson
Super User
It's definitely possible to unpivot in the query editor without breaking DirectQuery. It looks like you're trying to pivot though. This is also possible but it gives this error if you choose an aggregation other than min/max/avg/sum that it can easily translate back into a native query. Try picking one of those instead of Don't Aggregate.
drogzyFirst I hit Unpivot from other columns on the ID column.
Then I hit pivot on the attributes column, select values and then on advanced options I select don't aggregate.
This essentially gives me what I want which is to go from 2 rows that have same ID but the columns have info separately to one row with info smuged together as long as one row is null....the only issue is the direct query is broken. It actually breaks as soon as I hit unpivot from other columns.
Those options (sum, max, min) don't show up for me, this is what I see:
Appreciate your help on this!
- AlexisOlson
Hmmm. I think maybe it's because it isn't a number column.
What is the purpose behind unpivoting and pivoting? What are you ultimately trying to do?
I am trying unpivot while my table is in Direct Query mode. I am not able to apply the changes. It is asking to convert the table from DQ to Import. Any work around ?
