Power BI Pro vs Micrososft 365 VS Premium

[lememins]

Hi, 

I've got a direct query connecting via a SQL server.

When I edit the query (change a number to %, any data transformations) I get an error message saying 'This step results in a query that is not supported in DirectQuery mode.'

Is this a limitation of Power BI?
Or is this due to using Microsoft 365 version - i.e. will Pro or premium allow this to be done? 

Or is this a restriction in the settings? 

Thanks

[TeigeGao]

Hi @lememins ,

In your scenario, it is not a Power BI Pro vs Micrososft 365 problem, it is a limitation when you are using the Direct Querry mode, you can try to create a measure with the function FORMAT() to change it in the visual level.

Best Regards,

Teige

[lememins]

Teige, thanks fo rthe reply.

I might be wrong but I think the data transformation I want to do would be very complicated using measures - part of what I want to do is order the data by three different fields, then compare to the previous event and then calculate current - previous occurance.....unless you can direct me to an existing answer on this? (I found the create index and merge the tables which works in import mode quite simple to do)

[SteveCampbell]

This is a limitation of direct query.

Direct query recalculates every time you interact with the report, so for performance you cannot do much data manipulation, as this would have to be executed every time a user interacts with the report in any way.

I would reccomend using import if possible, the rfresh limits are quite good (8 per day for pro, 48 for premium), otherwise you are limited in data transformations.

[lememins]

Thanks Steve.

I'm not familiar with the import, but if I import and set up a re-fresh rate on the data would the calculation automatically be applied? If so this would solve all my issues! 🙂

Is there a link to set up this data refresh? (I've had a look and can't find it...maybe naming it wrong)

[SteveCampbell · Accepted solution]

Yes, all the steps you apply in the query editor will be calculated at the time of every refresh.

Data refresh is set up in the service (app.powerbi.com)

https://docs.microsoft.com/en-us/power-bi/refresh-data#data-refresh