Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

0

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

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:

RenePro_0-1675069019901.png

 

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:

RenePro_1-1675069019904.png

 

I tried manually refreshing the dataset and then got this message

RenePro_2-1675069019912.png

 

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

RenePro_3-1675069019917.png

 

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

RenePro_4-1675069019920.png

 

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

RenePro_5-1675069019928.png

 

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

RenePro_6-1675069019929.png

 

This is also not changeable

RenePro_7-1675069019930.png

 

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...

Status: Delivered

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.

Recording 2023-02-01 at 16.54.49.gif

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.

Recording 2023-02-01 at 17.01.48.gif

Best Regards,
Community Support Team _ Ailsa Tao

Comments
v-yetao1-msft
Community Support
Status changed to: Delivered

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.

Recording 2023-02-01 at 16.54.49.gif

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.

Recording 2023-02-01 at 17.01.48.gif

Best Regards,
Community Support Team _ Ailsa Tao

Analyst-Rene
Frequent Visitor

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
Frequent Visitor

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:

RenePro_0-1676984275114.png

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)

RenePro_1-1676984447256.png

(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)

RenePro_2-1676985205324.png

 

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

RenePro_3-1676985714521.png

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)

RenePro_4-1676985983060.png

 

Analyst-Rene
Frequent Visitor

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?

RenePro_0-1676986735989.png

 

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

RenePro_1-1676989273012.png

 

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?

RenePro_2-1676989391261.png

 

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

RenePro_0-1676989193935.png