Transition to Power BI Service Datasets / Power Query greyed-out

[PowerBSer]

Hello,

I am seeking help transitioning to Power BI Service data sources.

Origional approach:

Local data sources (excel sheets), manual refreshing of reports via PBI Desktop Editor.

New approach:

Host excel sheets on sharepoint (continually updated by multiple users), utilize scheduled refreshes.

Problem:

1.  PowerQuery editor is disabled when using Power BI Service data.

2.  Excel data must be formatted as a table prior to import to Power BI Service (slows down tracking sheets with large datasets).

Essentially I want to be able to query and transform the tracking sheets in an automated fashion and have the transformed data hosted on SharePoint so that I can pull it in to Power BI Service as a dataset without any manual intervention.

Are there any suggestions as to the best approach here?

Note: I thus far have been unable to have a Data Gateway installed due to internal organizational issues.  Also, I am aware that Azure may offer some solutions however, unless I am mistaken about licensing costs those solutions are cost-prohibitive.

Regards,

[v-piga-msft]

Hi @PowerBSer,

Firstly, if you get data with Power BI Service dataset in Power BI Desktop, there is a live connection so you cannot use Eidt Queries.

In addition, you could try with connect the excel worksheet in SharePoint online directly in Power BI Desktop.

Then you could use Edit Queries.

Best Regards,

Cherry

[PowerBSer · Accepted solution]

Thank you for your response.

However, I believe Edit Queries is disabled once you configure a Power BI Service data source, including documents hosted on sharepoint.

I am now thinking that I will have to go with a proper sql server to accomplish what I need.  Luckily, I found the right contact for support within the organization.

Thank you Cherry,

[v-piga-msft]

Hi @PowerBSer,

Yes, you're right. Once we connect with the Power BI Service datasets, the Edit Queries will be disabled, becase we cannot use Edit Queries under live connection.

It's glad that you have found a solution. Please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry