Direct query, not able to make changes

[Susan12344]

Hi all,

For PowerBI I use direct query to an SQL server because the data needs to be refreshed every few hours.

The current reports are not very nice, for instance the columns that display week name and month name are A-Z and not jan-dec. Since it's direct query I can't just change that by sorting the column. The only solution that I can see is to remove the current date fields and create a whole new date table but that would be a lot of husstle. Is there anyone that has some advice on this?

(there are multiple things that I would have been able to fix within minutes if I would have access to the regular PowerBI/PowerQuery functions.)

[BA_Pete]

Hi @Susan12344 ,

Firstly, Direct Query is generally used in the following two scenarios:

1) You need up-to-the-second data

...and/or...

2) Your dataset is billions of rows and too big to reasonably be imported

If your report/dataset does not meet either of these criteria, then I would recommend switching to import mode.

Secondly, I would strongly recommend that you create a separate date/calendar table as a matter of course. It's generally-accepted best practice, and gives you full access to Power BI's OTB time intelligence functions. It also makes it really easy to sort month names in chronological order.

Pete

[Susan12344]

Thank you! The dataset is indeed way to big to be imported. I already have a date/calender linked to many visuals, but like I mentioned- it's not really doing its job well. 

My question was more if I can avoid making a whole new table and linking the new stuff to all the visuals or if i have no other choice.

[BA_Pete · Accepted solution]

You can  safely add a month number column to either your fact table or your calendar table (I'd recommend the calendar table) in Power Query using just Date.Month([Date]). This will fold to the server and is, therefore, fine for use in a Direct Query model.

Once you have that column, you can select your month name column in the Modelling tab, and use the 'Sort by column' dropdown under Advanced settings to assign it to sort by Month number:

Pete