Import dataset shows as direct query in service

[Anonymous]

Hi guys

I've spent months on a complex dataset that uses two SQL Sources and some web sources (excel sheets on OneDrive). Now upon testing I see that the Power BI Service detects the dataset to be in directquery mode rather than import. The scheduled refresh option is not showing, instead it gives me the option the schedule a cache refresh. So here's what I've tried so far:

- Check all queries and all of them are set to import (the other options in the dropdown in the modelling area aren't even available in Power BI Desktop).

- Saved the PBIX as a new file, with a new name and into another workspace. Same result.

- Disabled all preview features, saved the PBIX again. Same result.

- Renamed the PBIX to a ZIP file and checked the DataMashup and Metadata files. There's no evidence that the files are any different from a dataset that is running normally in import. "IsDirectQuery" is always followed by Value=10, which seems to be correct (what I fathomed from comparing my problem report with one that is running normally).

I know that probably the best way to solve this is to create the report from scratch. But that would mean a lot of work especially since there are roughly 50 bookmarks and plenty of measures that I would need to create again.

Does anybody konw what elese could be tried so that the dataset is detected correctly? Anybody know how to read the content of the zip? Or anyone with a trick on how I can recreate the report the most efficient way?

Thanks a lot for the help!

[Anonymous]

Hi @Anonymous,

It sounds like your data source is pointed to the excel file which you publish to one drive side instead of mapping to the root database tables.

Data refresh in Power BI 

For this scenario, power bi service will trace your excel file and use onedrive and online refresh to handle your report. (notice: The refresh triggered when you excel file contents changes. In fact, the excel file who stored online not able to automatically update itself so these refreshes should not be triggered or refresh without new data updated)

After finished reading your description, I think you should try to connect to power bi data model(it is a SSAS instance with tabular mode) and try to use SQL server related tools to change the connection mode. (for Datasource mapping, you can do modification on query editor side)

connect to power bi desktop model from excel and ssms 

DirectQuery mode in tabular models 

BTW, you can also take a look at following blog if it helps:

Switching from imported data to DirectQuery or Live Connection in Power BI 

Regards,

Xiaoxin SHeng

[Anonymous · Accepted solution]

Seems like I was able to solve the problem and it was somewhat connected to the RangeStart and RangeEnd Parameters set up in my datamodel. Only taking them out of filter statements in the actual queries on PowerQuery did not suffice. I had to delete the two parameters alltogether.

Also, I followed the advice to take another source into the sane datamodel as a direct query source. Apply the query changes, and then delete it again afterwards.

Hopefully this will help when someone else faces the same issue.

[jeffshieldsdev]

Thanks, removing RangeStart and RangeEnd helped me too.

I have a report using Azure SQL DB and Dataflows.  All tables are set to Import.

Publishing to Service, Scheduled Refresh was missing and clicking Refresh on my dataset it would run instantly.

Now that I removed the RangeStart and RangeEnd, I'm able to refresh the dataset.

[GilbertQ]

Hi @Anonymous  What I would suggest is trying to make a change on one of the tables in Power Query which would force it to import the data.  And see if that works