Unpivot columns while connected with Direct Query

[drogzy]

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 

[AlexisOlson]

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.

[drogzy]

First 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?

[drogzy]

I am trying to make rows (typically 2) that have same ID become one because in most cells in these rows the info is duplicated;  or null in one and data in the other. 

I was using the pivot/unpivot just because I saw from another thread suggestion. It does what I want but it breaks the direct query. 

[AlexisOlson]

Maybe try grouping by the ID column and take the maximum over the others.

[drogzy]

Sorry, I am not sure what you mean

[AlexisOlson · Accepted solution]

Here's a simpler example.

Do this:

to get this: