I still have access to an intern's account who no longer works here. He created a report in "Service" under his "personal workspace" so there isn't an option to download the *.pbix file. So I ended up going to "Desktop" and I was able to select the same dataset he used in "Service" and I recreated the reports and published them to a "group workspace"; so I can edit the reports in the future and we can delete his account. My issue is how do I update data in this dataset? I can't go under "Edit Queries" in "Desktop" because it's grayed out. If I click on "Refresh" it looks like it refreshes properly but I have no idea where the source file is that it's refreshing from. I tried going back into "Service" under "Settings > Datasets" and when I click on that dataset all I see is the following. Refresh can't be scheduled because the data set doesn't contain any data model connections, or is a worksheet or linked table. To schedule refresh, the data must be loaded into the data model. Parameters haven't been defined for this dataset yet. If you want to set parameters, use the Query Editor. Why is it so difficult to just get to the actual source data so I can continue to update it with new data? If my company deletes this intern's Microsoft account, will this dataset also get deleted and not work with the new "group workspace" report I just created? Thank you!

It sounds like they might have created a new report on an existing dataset. If so...While logged in with their account in My Workspace, go to ReportsFor that report under actions click the "View Related" It'll show what dataset the report is usingGo to the Datasets tab (not in settings but in My Workspace)For that dataset under Actions, click the ellipsis and select download pbix.Republish the dataset and report and in group/app workspaceOpen your report in Power BI Desktop. Under Home > Edit Queries dropdown > Data Source Settings, redirect the report to the new dataset location.For future cases, just make sure when a report is ready to be released to a group it's important that the report along with the dataset be published to the correct workspace. Personal workspaces are just that, personal. You can also create a dev workspace to publish WIPs to, but be careful for any security concerns. Hope this helps. If not, screenshots would be helpful to better understand the issue.

HI Dali748 , If you were able to recreate the report that means you have access to the dataset. And this has nothing to do with the intern's Account. Now if edit queries is grayed out that means you have limited access to play with your data.You will have to request the person who provided the dataset to give you an access to edit queries. Also the refresh is set by the person who owns the dataset(view) Thanks,Tejaswi

Anonymous Thank you for the reply! I'm logged in both Power BI "Service" and "Desktop" with the intern's account; so I should have full access to the dataset since he's the one that created it. In "Desktop" I just selected the dataset and then recreated the report view, but I can't seem to find a way to actually see the dataset in "Edit Queries" because it's grayed out and I'm sure I have full access since I'm logged in as the intern who created it. Could it be because he initially created this dataset from "Service" and not "Desktop"?

Dali748 , If you connect to the power bi dataset, you can't edit the dataset in power query because it's in live connection mode. So if you want to modify the dataset or change the data model, you need to download the pbix file from power bi service and publish to replace the current dataset. Click File-> Download Report(Preview) to download the pbix file. Community Support Team _ Jimmy Tao If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Edit Queries grayed out - How can I view the source data in Excel and append data to this source?

13 Replies

Dali748
Helper II
6 years ago
I still have access to an intern's account who no longer works here. He created a report in "Service" under his "personal workspace" so there isn't an option to download the *.pbix file. So I ended up going to "Desktop" and I was able to select the same dataset he used in "Service" and I recreated the reports and published them to a "group workspace"; so I can edit the reports in the future and we can delete his account.

My issue is how do I update data in this dataset? I can't go under "Edit Queries" in "Desktop" because it's grayed out. If I click on "Refresh" it looks like it refreshes properly but I have no idea where the source file is that it's refreshing from.

I tried going back into "Service" under "Settings > Datasets" and when I click on that dataset all I see is the following.

Refresh can't be scheduled because the data set doesn't contain any data model connections, or is a worksheet or linked table. To schedule refresh, the data must be loaded into the data model.

Parameters haven't been defined for this dataset yet. If you want to set parameters, use the Query Editor.

Why is it so difficult to just get to the actual source data so I can continue to update it with new data?

If my company deletes this intern's Microsoft account, will this dataset also get deleted and not work with the new "group workspace" report I just created?

Thank you!
- v-juanli-msft
  Community Support
  6 years ago
  Hi Dali748
  
  If you don't need to change the data model, just need to recreate visuals or reports,
  
  I would suggest the creator of the report to republish the report from Power BI Desktop to an App workspace where you are a member with edit permission.
  
  https://docs.microsoft.com/en-us/power-bi/service-collaborate-power-bi-workspace
  
  In your scenario, when connecting to power bi dataset with power bi desktop,
  
  it is a live connection, so edit queries doesn't support.
  
  For your requirements, " I can edit the reports in the future and we can delete his account",
  
  You could use the PowerShell script below to take over the dataset in the Power BI Service.
  This will allow you to keep everything as is, but change the Owner.
  
  https://github.com/Azure-Samples/powerbi-powershell/blob/master/takeover-dataset.ps1
  
  Best Regards
  Maggie
  
  Community Support Team _ Maggie Li
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  - Anonymous
    Not applicable
    6 years ago
    v-juanli-msft - You are making a post to a user in the "Removed Posts" board, which is a private area.
    
    This user made two posts because his post was marked as spam. I unmarked them, and at the users request, removed one of the posts.
    
    Please see the thread here: https://community.powerbi.com/t5/Desktop/Edit-Queries-grayed-out-How-can-I-view-the-source-data-in-Excel/td-p/783891
PANDAmonium
Resolver IV
6 years ago
It sounds like they might have created a new report on an existing dataset. If so...
While logged in with their account in My Workspace, go to Reports
For that report under actions click the "View Related" It'll show what dataset the report is using
Go to the Datasets tab (not in settings but in My Workspace)
For that dataset under Actions, click the ellipsis and select download pbix.
Republish the dataset and report and in group/app workspace
Open your report in Power BI Desktop. Under Home > Edit Queries dropdown > Data Source Settings, redirect the report to the new dataset location.
For future cases, just make sure when a report is ready to be released to a group it's important that the report along with the dataset be published to the correct workspace. Personal workspaces are just that, personal. You can also create a dev workspace to publish WIPs to, but be careful for any security concerns. Hope this helps. If not, screenshots would be helpful to better understand the issue.
Anonymous
Not applicable
6 years ago
HI Dali748 ,

If you were able to recreate the report that means you have access to the dataset. And this has nothing to do with the intern's Account.

Now if edit queries is grayed out that means you have limited access to play with your data.
You will have to request the person who provided the dataset to give you an access to edit queries. Also the refresh is set by the person who owns the dataset(view)

Thanks,
Tejaswi
- Dali748
  Helper II
  6 years ago
  Anonymous Thank you for the reply!
  
  I'm logged in both Power BI "Service" and "Desktop" with the intern's account; so I should have full access to the dataset since he's the one that created it.
  
  In "Desktop" I just selected the dataset and then recreated the report view, but I can't seem to find a way to actually see the dataset in "Edit Queries" because it's grayed out and I'm sure I have full access since I'm logged in as the intern who created it.
  
  Could it be because he initially created this dataset from "Service" and not "Desktop"?
  - v-yuta-msft
    Community Support
    6 years ago
    Dali748 ,
    
    If you connect to the power bi dataset, you can't edit the dataset in power query because it's in live connection mode. So if you want to modify the dataset or change the data model, you need to download the pbix file from power bi service and publish to replace the current dataset. Click File-> Download Report(Preview) to download the pbix file.
    
    Community Support Team _ Jimmy Tao
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.