DirectQuery to Dataset Issue - ‘Make changes to this model’ missing

[Analyst-Rene]

DirectQuery to Dataset Issue

I started off connecting to various sources (SharePoint Online lists and Excel), months ago. They are all import, and I could publish my report. I added a DirectQuery to Azure DevOps through Advanced Editor in Power Query Editor and I still had no issues in publishing my report. I then added DirectQuery to a Power BI Datasets, using the Get Data option in Power BI Desktop BUT now when I try publishing, I get the following message:

My changes are not applied in the Workspace. Going to the service/my dataset that exists in a shared workspace I created, as I have access to create workspaces – I see the following:

I tried manually refreshing the dataset and then got this message

I have no issues in Power BI Desktop and I can refresh, so assume the online service is unable to refresh the dataset. So, I had a look online and found this guide Using DirectQuery for live connections, showing exactly what I am trying and that you can add this type of connection but that “Using DirectQuery for Power BI datasets and Analysis Services requires your report to have a local model.”. The issues I know face is that I do not have this option.

What the guide says

What I see in my report, I do not have ‘Make changes to this model’

I found another article, where you could be able to set the storage mode but for me, the option is greyed out

What I should see

What I see for DirectQuery to Power BI Dataset tables, the options is greyed out and I cannot change it

This is also not changeable

There are 3 shared Workspaces and I have admin access to be able to create shared workspaces. I am an admin on each of them so have full access to the datasets. I am the publisher of on one of the Datasets.

Would appreciate it if anyone could let me know if they have faced this issue and if they managed to find a solution. According to the guides above, this should be possible.

I have posted, asking for help but one of them is closed with no real resolution and the other is old with no updates:

externally on other threads

https://community.powerbi.com/t5/Service/This-dataset-includes-a-dynamic-data-source-Since-dynamic-d...

https://community.powerbi.com/t5/Desktop/Can-t-Make-Changes-to-Model-After-Connecting-to-Dataset/td-...

Starting my own thread in Desktop, Service and Power Query respectively

https://community.powerbi.com/t5/Desktop/Missing-Make-changes-to-this-model-option-in-desktop/m-p/30...

https://community.powerbi.com/t5/Service/Service-unable-to-refresh-dataset/m-p/3044815

https://community.powerbi.com/t5/Desktop/Can-t-Make-Changes-to-Model-After-Connecting-to-Dataset/m-p...

[v-yetao1-msft]

Hi @Analyst-Rene 

When you connect to the dataset alone, you will find that its connection mode is Live connection . The function you mentioned is to convert LC mode to DQ mode, and the table you provided in the screenshot is already in DQ mode, so there is no relevant conversion button.

You can create a new report, then directly select dataset as the only data source, turn on all the relevant parameters and test it, and you will see the relevant buttons.

Best Regards,
Community Support Team _ Ailsa Tao

[Analyst-Rene]

Thank you @v-yetao1-msft , I will have to start from the beginning and rebuild my report but I did do an initial test and I connected to the Datasets first and I do have the option then. I then added the other sources; a SQL databases and and Excel spreadhsheet in SharePoint and a Sharepoint list using Import. It did allow me to pubsh it.

So I suppose the lesson here is that if you add a Dataset to an existing model with 'Mixed' storage mode already, you won't get the option. You only get the oftion if you stared off with adding the Datasets in the model, before adding other data connection types.

[Analyst-Rene]

Hi @v-yetao1-msft, I still seem to be running into issues. I have started from scratch and added the Dataset connections first, then SharePoint, SQL databases, ADO etc. At first, I didn't add any visuals, I wanted to see if I could publish the report and I can now publish it but I still face an issue. I ahve since added visuals and published but I can't open the report online.

When I try to open the report online, I get this error that I have not seen before:

It says it published OK but with the following caviate (wich I know about and is a seperate problem am looking into as it won't let me refresh the dataset online. It has to do with the query I am using I believe)

(when I try to refresh the database , I get - but again, that is a seperate matter but if you have a solution for this as well, that would be great)

I know it has to do with the DirectQuery I am using through Query Editor:

let
Content = "
{
""query"": ""Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.TeamProject] = 'TeamProjectn' AND [System.WorkItemType] = 'Feature' AND [State] <> 'Closed' AND [State] <> 'Removed'""
}
",

Response = Web.Contents("https://dev.azure.com/Company/TeamProject/_apis/wit/wiql?api-version=7.0",
[
Content = Text.ToBinary(Content),
Headers=[Authorization="Basic OmtrZHR3NXVyZXVtaDRiamJrcmhzbGhjN2dkdHRlZ2d6YWtxY3FkZGhwYTQzeGJweTNqNXE=", #"content-Type" = "application/json"]
]
),
Json = Json.Document(Response),
workItems = Json[workItems],
tablefromList = Table.FromList(workItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

...etc...

So I know I need to look into a different way of adding ADO at some point

BUT, it does not explain why I cannot open the report at all online

It isn't to do with the ADO connection because if I publish the report with just that connection, it is fine and I can open the report online (just can't refresh the dataset, wich is fine as I can manyally publish updates as a workaround for now)

[Analyst-Rene]

Hi @v-yetao1-msft  - not to worry, I found a solution. Deleting the report and dataset and then republishing it seems to have fixed the issue 🙂 as per https://community.powerbi.com/t5/Desktop/powerbi-com-unable-to-open-a-report/m-p/662697#M318482

On a side note, is there a better way to add ADO so that I can schedule a dataset refresh?

The sections where you would normally add any Gateways and Credentilas is now dissapear, even though I need them for data sources

I should see (as I do with other reports) - any reason why it is no longer available? Should I not at least be given the option to set credentials for the other data sources?

Althou, I don't understand why I don't still get the Gateway and Authentication options anyways, since other connection in my model can't connect at all